One million rows question??? How to optimize? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

One million rows question??? How to optimize?

Author

Topic

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-02-22 : 18:44:36

I have a table which has 1.2 million rows.
The clustered index is on a field called DYH (date year hour), the values could be like 1001 2001 7062 etc etc. the fields is an integer.
plus there is another field which has non clusterd index on it it is a char(1) field and can have only 'y' or 'n'. this represents the cs or mf field.

my query is like this...i have changed the column names for simplification...

SELECT
a,-- varchar(10)
b, -- varchar(30)
c, -- varchar(30)
d,-- varchar(10)
e,-- -- varchar(20)
f,-- varchar(10)
g,---- varchar(50)
h,-- varchar(50)
i, -- varchar(100)
j,-- varchar(10)
k, -- varchar(10)
l, -- varchar(10)
m, -- varchar(10)
n, -- varchar(10)
max(o), -- this is a date field
dyh -- this is a integer field
FROM tabletest
where (dyh between 7062 or 7211 or dyh between 1062 or 1211 or dyh between 2062 or 2211
or dyh between 3062 or 3211 or dyh between 5062 or 5211)
and ( cs ='Y' OR mf ='Y' )
group by
a,
b,
c,
d,
e,
f,
g,
h,
i,
j,
k,
l,
m,
n
order by i

How can I optimizae this query. I need a response time of 10 seconds or less... how ever it takes 3 minutes to return....

if I only use one dyh condition i.e. between 7062 and 7121 , it takes 10 seconds and as I add more dyh conditions it increases time.
i.e. when I make it between 7062 and 7121 or between 1062 and 1121 and so on.... for 3rd and 4th conditions.... there can be a total of 7 conditions like this each represneting day of week...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-22 : 18:51:33

You have to show us your table structure and all indexes.

Also -- why are you grouping on that many columns? Looks like you have a really bad table design with either duplicates or all kinds of data stuffed into one big table.

In short -- we need a lot more info before we can help you out.

- Jeff
http://weblogs.sqlteam.com/JeffS

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-02-22 : 19:24:52

My Table structure is like this...

COLUMN A, COLUMN B, COLUMN C, COLUMN D, COLUMN E, COLUMN F, COLUMN G
AE15897 46545464 GEORGE HILL REGION SAM JAKE 87878999 PATEL INC
AE15896 46545463 MATT HILL STATE JOSH JAKE 17878999 SATEL INC

COLUMN H, COLUMN I, COLUMN J, COLUMN K, COLUMN L,
HILL AREA REGION HILL AREA MANAGEMENT TEAM SVP ALC COBB AREA REGION COBB AREA MANAGEMENT TEAM VP BLC

COLUMN M, COLUMN N , COLUMN 0 , COLUMN P

IS BASICALLY A MAX 0F DATE FIELD (HAS A VALUE OF 02/02/2008 ) E.G.
DYH IS AN INTEGERE FIELD, WITH VALUES LIKE I MENTIONED ABOVE.

I NEED O GROUP BY DYH, BECAUSE IF SOME THING FALLS INSIDE 7001 WHICH BASICALLY MEANS 7 FOR SATURDAY 00 FOR HOURS ZERO ZERO I.E. MIDNIGHT AND 1 FOR HALH HOUR INCREMENT AND E.G 7121 WHICH BASICALLY MEANS SATURDAY NOON AND FIRST HALKF HOUR I.E. 12:30. THEN I NEED TO GET THE OTHER DATA. I.E. COLUMNS A, B, C,D,E,F,G,H ETC

I CAN GET A,B,C,D,E,F,G,H FROM OTHER TABLES AS WELLL, HOWEVER I DECIDED TO PUT THEM IN THE SAME TABLE TO THAT ACCESS WOULDBE FAST AS THERE WILL BE LESS JOINS.

THEER ARE ONLY THOSE THREE INDEXES ON THE TABLE THAT I AM USING...

YES THERE ARE DUPLICATES IN THE TABLE, BUT IF REMOVE THE DUPLICATES AND
GET THE DATA FROM THE SOURCE TABLE SUSING JOIN.... THEN I WILL STILL HAVE TO GROUP BY AS WILL NEED TO GROUP BY COLUMN 'F'... AND OTHER DTA IF I AM GETTING FROM OTHER TABLES NEED TO BE IN A SELECT CLAUSE THEN EVEN IF I GET FROM OTHER TABLES I WILL STILL NEED TO GROUP BY THEM ANY WAY, SO WHY GO TO THE SOURCE TABLES?
THANKS FOR YOUR HELP...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-22 : 19:29:12

jsmith is right..messy at best...

You can simplify the conditional..since it appears based solely on your example that the right 3 digits of the integer need to be 62 to 121


where right (dyh,3) >= 62 and right (dyh,3) <= 121 and ( cs ='Y' OR mf ='Y' )

Make sure that all of those group by's are necessary...it Might be faster to do a left or inner join to a subquery which does the max date and link to the main table--that way you don't need the massive group by with an order (that is a load for the tempdb to handle).

But more can be offered if you provide actual structure with index information.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources