Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Best Way to Speed Up Queries Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 8:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:48 AM
Points: 125, Visits: 361
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
Post #1437946
Posted Tuesday, April 2, 2013 9:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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)
Post #1437950
Posted Tuesday, April 2, 2013 9:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,899, Visits: 32,113
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1437952
Posted Tuesday, April 2, 2013 9:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
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 2008, MVP
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

Post #1437955
Posted Tuesday, April 2, 2013 11:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:48 AM
Points: 125, Visits: 361
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
Post #1438021
Posted Tuesday, April 2, 2013 11:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:48 AM
Points: 125, Visits: 361


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
Post #1438022
Posted Tuesday, April 2, 2013 11:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
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 2008, MVP
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

Post #1438030
Posted Wednesday, April 3, 2013 7:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
'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
Post #1438321
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse