How to improve sql server query performance

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

0	Refer following links. http://msdn.microsoft.com/en-us/library/ms172984.aspx http://tonesdotnetblog.wordpress.com/2008/05/26/twelve-tips-for-optimising-sql-server-2005-queries/ Jpchoudhari, if this helps please login to Mark As Answer. \| Alert Moderator

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

0	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

Latest Posts