Large tables and slow queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Large tables and slow queries

Author

Topic

creature
Starting Member

4 Posts

Posted - 2009-04-20 : 10:46:32

Hi, I have a website that uses an SQL server 2005 database, basic select queries are begining to take too long to complete. Ive already optimised the quieries as best i can and used indexes which helped a little, but the table is growing rapidly and contains around 20 million rows, with around 28,000 records inserted daily.

Im wondering if anybody has any ideas on how i can speed up queries without making changes to the web application. Ive considered table partitioning which made a huge difference in a test environment, but this is only a feature of the enterprise edition which is just too expensive at present.

Any help is greatly appreciated

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-04-20 : 10:51:38

Are you maintaining the indexes i.e. checking for fragmentation etc , updating stats the simple things to start with

creature
Starting Member

4 Posts

Posted - 2009-04-20 : 11:16:54

Thanks for the response.

Yes, I have a maintainence plan to rebuild the indexes and recompute the stats once per week

All of the queries rely on the use of foreign keys to retrieve data between 2 dates. I have indexed all FK's and the date column.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-20 : 11:33:24

have you looked at the execution plan? That will tell you which parts of your code are taking the time.

Also -- post your SQL. Many eyes can spot issues you haven't thought of.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

creature
Starting Member

4 Posts

Posted - 2009-04-20 : 13:14:19

Ive not used the execution plan, these queries are pretty basic, around a year ago they took around 5 seconds to execute and display, but are gradually taking longer and now execute in approx 20 seconds(1 minute plus, before i added indexes). The server itself is under hardly any load, Ive assumed that its down to how much data is now in the table as it was a lot slower before I added indexes and working from a sub set of that data its quicker again, hense why i figured to try partitions.

The following columns are indexed
intSiteID
intMeterID
intChannelID
datReadingDateTime

Anyway, the table consists of;

[tbl_data](
[intDataID] [bigint] IDENTITY(1,1) NOT NULL,
[intCompanyID] [int] NOT NULL,
[intSiteID] [int] NOT NULL,
[intMeterID] [int] NOT NULL,
[intChannelID] [int] NOT NULL,
[datReadingDateTime] [datetime] NULL,
[decKWH] [decimal](18, 5) NULL,
[decCO2] [decimal](18, 5) NULL,
[dblCost] [decimal](19, 5) NULL,
[dblCostReal] [decimal](19, 5) NULL,
[strUtility] [varchar](64) NULL,
[strUnits] [varchar](24) NULL,
[intTariffItemID] [int] NULL)

and here is the select query that takes approx 20 secs to execute

ALTER PROCEDURE [dbo].[sp_Get_ChartData_kWh]
@intCompanyID int,
@intMeterID int,
@intChannelID int,
@datStart datetime,
@datEnd datetime
AS
SELECT datDataPolled, decKWH, intMeterID, strUnits
FROM tbl_data
WHERE intMeterID = @intMeterID
AND intChannelID = @intChannelID
AND datDataPolled >= @datStart
AND datDataPolled <= @datEnd
AND intCompanyID = @intCompanyID
ORDER BY datDataPolled ASC

The query generally pulls back a months worth of records, approx 1400 rows

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-20 : 13:22:34

Compare the execution plan of hard-core select statements vs Stored procedure? Look like parameter sniffing issue.

hi2sunil
Starting Member

3 Posts

Posted - 2009-04-20 : 14:04:35

Hi,

Try changing your query "AND datDataPolled >= @datStart AND datDataPolled <= @datEnd" to "AND datDataPolled Between @datStart and @datEnd" or
"AND datDataPolled Between Cast(@datStart as VarChar(20)) and Cast(DateAdd(mi,-1,DateAdd(d,1,@datEnd)) as VarChar(20)) " --To Include time till 11:59 PM for EndDate.

Thanks
Sunil Kovvur

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-21 : 04:12:14

Good Idea Sodeep!

I know it sounds ridiculous but if you declare a bunch of local variables in your stored proc and then assign them the values from the parameters passed into the stored proc. Then use those local variables it can make a shocking difference sometimes!

Once saw a 100 fold speed-up with one of our stored proc here.

I remember suggesting it to our engineering manager by starting thus:

"Well -- I know this sounds insane but bear with me....."

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-21 : 04:45:26

Also check if you have one index similar to this

CREATE NONCLUSTERED INDEX	IX_SQLTeam
ON				tbl_data
				(
					intMeterID,
					intChannelID,
					datDataPolled,
					intCompanyID
				)
INCLUDE				(
					deckKWH,
					strUnits
				)

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-21 : 05:01:28

If you are using Enterprise Edition you can try out partitioning.

E 12°55'05.63"
N 56°04'39.26"

creature
Starting Member

4 Posts

Posted - 2009-04-22 : 05:16:49

Thanks for the responses, you've given me a few things to try out.

regarding partitioning, I only have the standard version of sql 2005 on our production server, so have been considering table views to get smaller subsets of data kind of similar to partitions.

Ill try out the other suggestions before i test the benefit of using views, but has anyone else seen any speed benefits by using them in the past?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 05:27:37

Yes.
I have a two blog posts here about partitioning.

First Enterprise Style
http://weblogs.sqlteam.com/peterl/archive/2008/06/12/Horizontal-partitioning-Enterprise-style.aspx

And then partitioned views
http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Horizontal-partitioning.aspx

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources