fine tune query

Question

fine tune query

shwetha

Ten Centuries

Points: 1248
More actions
January 3, 2007 at 4:26 pm

#116751

Can someone please tell if any tweaking required -
SELECT GBOBJ, GBLT, GBFY, [GBAPYC]+[GBAN01] AS JanTotal, [GBAPYC]+[GBAN01]+[GBAN02] AS FebTotal, [GBAPYC]+[GBAN01]+[GBAN02]+[GBAN03] AS MarTotal, [GBAPYC]+[GBAN01]+[GBAN02]+[GBAN03]+[GBAN04] AS AprTotal, [GBAPYC]+[GBAN01]+[GBAN02]+[GBAN03]+[GBAN04]+[GBAN05] AS MayTotal, [GBAPYC]+[GBAN01]+[GBAN02]+[GBAN03]+[GBAN04]+[GBAN05]+[GBAN06] AS JunTotal, [GBAPYC]+[GBAN01]+[GBAN02]+[GBAN03]+[GBAN04]+[GBAN05]+[GBAN06]+[GBAN07] AS JulTotal, [GBAPYC]+[GBAN01]+[GBAN02]+[GBAN03]+[GBAN04]+[GBAN05]+[GBAN06]+[GBAN07]+[GBAN08] AS AugTotal, [GBAPYC]+[GBAN01]+[GBAN02]+[GBAN03]+[GBAN04]+[GBAN05]+[GBAN06]+[GBAN07]+[GBAN08]+[GBAN09] AS SepTotal, [GBAPYC]+[GBAN01]+[GBAN02]+[GBAN03]+[GBAN04]+[GBAN05]+[GBAN06]+[GBAN07]+[GBAN08]+[GBAN09]+[GBAN10] AS OctTotal, [GBAPYC]+[GBAN01]+[GBAN02]+[GBAN03]+[GBAN04]+[GBAN05]+[GBAN06]+[GBAN07]+[GBAN08]+[GBAN09]+[GBAN10]+[GBAN11] AS NovTotal, [GBAPYC]+[GBAN01]+[GBAN02]+[GBAN03]+[GBAN04]+[GBAN05]+[GBAN06]+[GBAN07]+[GBAN08]+[GBAN09]+[GBAN10]+[GBAN11]+[GBAN12] AS DecTotal
FROM TABLE2
WHERE GBOBJ between '1000' and '9000' AND GBLT='AA' AND GBFY= 06 and gbco <> '00999' Order by GBOBJ

SELECT count(*)
FROM TABLE2
WHERE GBOBJ between '1000' and '9000' AND GBLT='AA' AND GBFY= 06 and gbco <> '00999'
SELECT count(*) FROM TABLE1WHERE ( GMCO BETWEEN '00001' AND '00999' ) --ORDER BY GMCO ASC,GMOBJ ASC
SELECT * FROM TABLE1WHERE ( GMCO BETWEEN '00001' AND '00999' ) --ORDER BY GMCO ASC,GMOBJ ASC
SELECT gmco, count(*) FROM TABLE1WHERE ( GMCO BETWEEN '00001' AND '00999' ) group by GMCO

this query runs absolutely fine/fast when run from access database however when run from query analyzer it takes much longer.

any suggestions?
Regards,

Viewing 4 posts - 1 through 3 (of 3 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Well, that's kind of a wide open question. Much of what we need to effectively tweak these queries has been omitted from your post. What does the query plan look like? It would help if you could post your table DDL (including indexes) and some sample data as well as the query plan.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

shwetha Ten Centuries Points: 1248 More actions · Answer 2

Thanks John, will provide that info tomorrow since I do not have access to that information right now -

thanks again!!!

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 3

Does the first select statement ever change? If not you could create a temp table or maybe better a view and just run the one select statement.

Just a suggestion

Thanks,

Kris