How to improve sql server query performance

Posted by Jpchoudhari under Sql Server on 8/30/2011 | Points: 10 | Views : 1471 | Status : [Member] | Replies : 5
Hi,

I am creating a module for which i am recording users IP address while user is browsing through the site and later on using this data to generate report as of Google analytic.

We have database that contains table like ISP,Location,Company based on various ip number ranges.

for ex.
ID StartIP EndIP Organization
1021 16874455 14587965 ABC

This tables contains huge number of records (around 70-80 lacks or rows per table) and I am joining such 5-6 tables to extract various data. This query takes lots of time so i am trying to find solution on boosting the query performance.

Can any body tell me solution for this. I have already put index on columns that i used in search criteria.. mostly IP range. Any other things that should be taken in mind.

Kind Regards,
Jay



Responses

Posted by: Rickeybglr on: 8/30/2011 [Member] Starter | Points: 25

Up
0
Down
try to minimize the use of joins..if required use inner join. dont use comma (,) in ur join query (use generally whn we use alias) try to use keywords instead of ths bcoz in background when we use comma it actually perform cross join whch take lot of time to execute query.

Jpchoudhari, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandiranjithreddy on: 8/30/2011 [Member] Starter | Points: 25

Up
0
Down
Instead of writing Query use view or based on your select cloumns try to partition the tables or analayze is there any sort operations or I/O operations......n etc to deliver high peformance ................... All the best.

Jpchoudhari, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/21/2012 [Member] Bronze | Points: 25

Up
0
Down
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use the correct transaction isolation level

Jpchoudhari, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/21/2012 [Member] Bronze | Points: 25
Posted by: Sriramnandha on: 6/26/2012 [Member] Starter | Points: 25

Up
0
Down
U can use

1) Index
2) joins
3) Avoid Cursor
4) follow Normalization Processs.
5) Remove duplication of Records
6) use Partition
7) isolation level

Regards


sriram

Jpchoudhari, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response