query performance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » query performance

34 posts, Page 1 of 4

1 2 3 4 »»»

query performance

Rate Topic

Display Mode

Topic Options

Author

Message

kk1173

Posted Monday, March 04, 2013 4:53 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:54 PM
Points: 30, Visits: 63

I have a table with 6 million rows. The table has around 200 columns. The data can be sliced and diced by the user in a number of ways.
Are there any tips of improving query performance other than adding indexes?
Currently some of the queries take around 40 seconds.

thanks
KK

Post #1426523

Jeff Moden

Posted Monday, March 04, 2013 8:20 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757

There are a couple of suggestions.

1. Make sure the queries are actually SARGable where an INDEX SEEK followed by an internal scan can be accomplished.
2. Possibly, normalize the table into smaller tables. Of course, this is highly data dependent. For example, call detail rows will frequently have 200 different pieces of information that are just about impossible to normalize.
3. Use indexed views. Of course, this can take a fair bit of space but allows you to virtually have more than 1 clustered index for some breakneck speed.

There are other suggestions such as doing pre-aggregation if a pivot or crosstab is involved but those are the big 3 that quickly came to mind.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1426574

kk1173

Posted Tuesday, March 05, 2013 11:52 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:54 PM
Points: 30, Visits: 63

I have the following questions
1. What does SARGable mean?
2. Wouldn't normalization decrease performance, since it would involve joining of multiple tables?
3. Would partitioning the table help?

Thanks

Post #1426968

Jeff Moden

Posted Tuesday, March 05, 2013 12:40 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757

kk1173 (3/5/2013)

1. Pretty much what I said. It means that the criteria was written in such a manner as to allow INDEX SEEKs to happen if the correct index is available. This generally means that you have no columns within a formula (especially but certainly not limited to aggregates) but also means that the criteria is tight enough to return only what is necessary. That last part helps defeat accidental many-to-many joins.

2. Correctly done, normalization can actually help improve performance because it makes the rows in the tables much more narrow. Skinny rows mean more rows per page. More rows per page means fewer reads (logical or otherwise). Fewer reads frequently results in faster speeds even in the presence of joins. Notice I never used the word "always" anywhere in that. Like anything else, "It Depends" on proper design and indexing.

3. No. Partitioning doesn't necessarily help for performance and usually doesn't help at all especially when otherwise performance challenged code is present. Correct partitioning's biggest advantage is that it makes it so you don't have to rebuild huge indexes on single partitioned tables during maintenance periods. There are some huge disadvantages to partitioning if you're using things like an IDENTITY column as the PK on the table. Partitioning also makes it easier to add new data and archive old data. Even then, the best reason to partition is still to reduce index maintenance times.

--Jeff Moden

Post #1426992

Sean Lange

Posted Tuesday, March 05, 2013 12:42 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 8,547, Visits: 8,204

kk1173 (3/5/2013)

I have the following questions
1. What does SARGable mean?

SARGable means Search Argument-able. http://en.wikipedia.org/wiki/Sargable

2. Wouldn't normalization decrease performance, since it would involve joining of multiple tables?

That is why he said possibly.

3. Would partitioning the table help?

Possibly.

The real challenge here is that your question is sufficiently vague enough that nobody can really tell you how to improve performance. We can't see your screen, we aren't familiar with your data structures or data. From my experience when a table has 200 columns there are some structural challenges that need to be dealt with. It also sounds from your description that users are doing a "catch-all" of searching through this table. Without knowing the structures and what you are trying to do the best anybody can do is take a shot in the dark.

_______________________________________________________________

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

Post #1426996

kk1173

Posted Tuesday, March 05, 2013 12:59 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:54 PM
Points: 30, Visits: 63

These are some really good suggestions. I will try these and let you know if i see any performance gains or have any other questions

Post #1427012

kk1173

Posted Tuesday, March 05, 2013 1:21 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:54 PM
Points: 30, Visits: 63

to give you an idea of type of queries that are being run

SELECT minormkt
,Sum(admits) as 'Total Admits' ,Sum([days]) as 'Total Days' , sum(OppAdm) as 'Opp Adm'
,sum(OppTotDays) as 'Opp Tot Days', sum(paid_amt) as 'Paid Amt',sum(bill_amt) as 'Bill Amt'
, sum(ReadmitOpportNet) as 'Readmit Oppor Net' ,sum(readmit_expected) as 'Readmit Expected'
, sum(Readmit_Factor) as 'Readmit Factor',sum(ER) as 'Total ER', ISNULL(sum(ER)/NULLIF(sum(Admits),0),0) as '% ER'
,ISNULL(sum([Days])/NULLIF(sum(Admits),0),0) as 'LOS' , ISNULL(sum(ExpDays)/NULLIF(sum(Admits),0),0) as 'Expected LOS'
,ISNULL(sum(OppTotDays)/NULLIF(sum(OppAdm),0),0) as 'Opport Admit LOS',ISNULL(sum(OppExpDays)/NULLIF(sum(OppAdm),0),0) as 'Opport Admit Expected LOS'
,ISNULL(sum(Readmit_Los)/NULLIF(sum(Readmit30),0),0) as 'ReadmitLOS',ISNULL(sum(Readmit_Allowed)/NULLIF(sum(Readmit30),0),0) as 'ReadmitAllowedPerCase'
,sum(ReadmitOpportNet)*ISNULL(sum(Readmit_Allowed)/NULLIF(sum(Readmit30),0),0) as 'ReadmitOpportNetSavings'
,ISNULL(sum(OppDaysPos)/NULLIF(sum(Admits),0),0) as 'Opport Days/Admit'

FROM vw_FactData a where 1=1
and ( region IN(SELECT Param FROM dbo.uf_AppendString('WEST','CENTRAL' )))
and ( [plan] IN(SELECT Param FROM dbo.uf_AppendString('MCR','COM')))
and( year(adm_dt) IN(SELECT Param FROM dbo.uf_AppendString('2011','2012')))
group by minormkt order by a.minormkt

The FROM vw_FactData is a standard view that further joins the base table (6 mill rows) with some look up tables.
I will remove the function in the WHERE clause to make it SARGable.
Any other suggestions?

Post #1427023

Sean Lange

Posted Tuesday, March 05, 2013 1:25 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 8,547, Visits: 8,204

I assume these queries must be being generated "on the fly".

In addition the scalar function you have year(adm_dt) which is also going to render that nonSARGable.

It is impossible to offer much real advice here because we still can't see what is going on. What is the definition of the base tables, what about the view? What are the indexes on the base tables? Is this an indexed view, etc etc etc.

_______________________________________________________________

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

Post #1427026

kk1173

Posted Tuesday, March 05, 2013 1:31 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:54 PM
Points: 30, Visits: 63

Yes, there is a stored procedure that build dynamic sql. This query above is just a PRINT of the sql that gets executed.
If you look at the query, there are SUM functions on various fields and also formulas like ISNULL(sum([Days])/NULLIF(sum(Admits),0),0).
I know its very hard to tell without looking at the data structure, but is there a better way of doing this aggregation rather than 'on the fly'?

Thanks
KK

Post #1427028

Sean Lange

Posted Tuesday, March 05, 2013 1:37 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 8,547, Visits: 8,204

kk1173 (3/5/2013)

Maybe.

Nobody can offer suggestions on improvement without first knowing the scope of the issue. It is not that it is difficult, it is impossible.

_______________________________________________________________

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

Post #1427029

« Prev Topic | Next Topic »

34 posts, Page 1 of 4

1 2 3 4 »»»

Permissions