Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Way to Speed Up Queries


Best Way to Speed Up Queries

Author
Message
timotech
timotech
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 447
Good Day,
Please i need to speed up the runtime execution of queries on my online server, this database has over a million records in separate tables, and i've already indexed the tables using date fields. Please is there anyother way of speeding up my queries from my application online. I use Stored Procedures for querying the database. Its more of a business Intelligence Portal of viewing charts and information.

Thanks

Tim
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16539 Visits: 16993
timotech (4/2/2013)
Good Day,
Please i need to speed up the runtime execution of queries on my online server, this database has over a million records in separate tables, and i've already indexed the tables using date fields. Please is there anyother way of speeding up my queries from my application online. I use Stored Procedures for querying the database. Its more of a business Intelligence Portal of viewing charts and information.

Thanks

Tim


Query performance is really more art than science. There is no magic "make all my queries go fast" solution. The queries need to be analyzed. There are lots and lots of things that can affect query performance. If you want some help with some specific queries we can help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14931 Visits: 38920
As Sean has identified, all we can offer is vague advice unless ou have some details we can look at to try and help.

here's a quick list of some things to look for, in general:

· How much Data is being returned? are there millions of rows, or columns with huge varbinary/image/varchar maxes being returned?·
· Are there clustered indexes on all the tables involved?
· Are there indexes to support the query?
· Is There a WHERE statement being used? if not...that's a table scan of all the data.
· Are there columns that are being selected that are not used (ie a VIEW of 50 columns, but you need only three columns)
· Are the statistics up to date?
· Are you using catch all queries?
· Are the WHERE statement parameters SARG-able?
· Are any functions being used in the WHERE statement?
· Are there any inline SELECT statements to get a a single or correlated value (i.e. ColumnName = (SELECT...)
· Could the Indexes benefit from INCLUDE columns?


Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47175 Visits: 44355
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


timotech
timotech
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 447
Lowell (4/2/2013)
As Sean has identified, all we can offer is vague advice unless ou have some details we can look at to try and help.

here's a quick list of some things to look for, in general:

· How much Data is being returned? are there millions of rows, or columns with huge varbinary/image/varchar maxes being returned?·
· Are there clustered indexes on all the tables involved?
· Are there indexes to support the query?
· Is There a WHERE statement being used? if not...that's a table scan of all the data.
· Are there columns that are being selected that are not used (ie a VIEW of 50 columns, but you need only three columns)
· Are the statistics up to date?
· Are you using catch all queries?
· Are the WHERE statement parameters SARG-able?
· Are any functions being used in the WHERE statement?
· Are there any inline SELECT statements to get a a single or correlated value (i.e. ColumnName = (SELECT...)
· Could the Indexes benefit from INCLUDE columns?


Well, to answer your questions
1. maximum number of records returned is always around 15000 records
2. The tables involved have clustered indexes
3. Definitely they use where statements
4. no
5. yes
6. i don't know what u mean by SARG-able
7. no functions in the where statements
8. Yes
9. I don't know

Thanks

Tim
timotech
timotech
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 447


Thanks, i've already seen this links before and practised some of the suggestions. My question is that can i make my queries run more faster, since they are online on another server.

Thanks

Tim
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47175 Visits: 44355
timotech (4/2/2013)
My question is that can i make my queries run more faster, since they are online on another server.


You probably can, same process, identify the poorly performing areas, fix them so they're not poorly performing, repeat until overall performance is acceptable.

There's no silver bullet here, no secret option, so setting, no switch. Tuning queries involves finding the bottlenecks, fixing them and doing that again and again until the problem's fixed.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLCharger
SQLCharger
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 1400
'online on another server'
Does this mean you are querying through a linked server?
If so, you need to ensure that the account used can see the statistics of the remote objects (read-only won't cut it)

Cheers,

JohnA

MCM: SQL2008
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search