Best Way to Speed Up Queries

Question

Best Way to Speed Up Queries

timotech

SSCrazy

Points: 2698
More actions
April 2, 2013 at 8:55 am

#272700

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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 SSCrazy Points: 2698 More actions · Answer 4

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 SSCrazy Points: 2698 More actions · Answer 5

GilaMonster (4/2/2013)
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/

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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 SSCommitted Points: 1958 More actions · Answer 7

'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