SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 overheat performance query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 01/05/2012 :  18:44:06  Show Profile  Reply with Quote

Good evening i've got this query but according to execution plan (table scan) it takes to much time to run this query (nearly 2 hours)even thouh is just for 1 day.

DECLARE @FECHA_INI CHAR(8)
DECLARE @FECHAFIN CHAR(8)
SET @FECHA_INI = '20120103'
SET @FECHAFIN = '20120304'
WHILE @FECHA_INI < @FECHAFIN
BEGIN
SELECT @FECHA_INI,COUNT(1) CANTIDAD
FROM ACA_INCIDENCIA
WHERE ((FEC_CREACION >= @FECHA_INI AND FEC_CREACION < @FECHAFIN)
OR (FEC_REAL >= @FECHA_INI AND FEC_REAL < @FECHAFIN)
OR (FEC_HISTORICO >= @FECHA_INI AND FEC_HISTORICO < @FECHAFIN))
AND FUENTE = 1
SET @FECHA_INI = CONVERT(VARCHAR,DATEADD(DD,1,@FECHA_INI),112)
END

And i modify the query cuting off the 2 lines containing the operator AND and the execution plan turn and index seek and the took 10 minutes OK (great) but the resulset is different fom the first one
The index for the table are:IX_ACA_INCIDENCIA|nonclustered located on INDICES|TIP_INCIDENCIA
IX_ACA_INCIDENCIA_1|nonclustered located on PRIMARY|FEC_CREACION
IX_ACA_INCIDENCIA_FUENTE|nonclustered located on INDICES|FUENTE
PK_ACA_INCIDENCIA|nonclustered, unique, primary key located on INDICES|NUM_INCIDENCIA, FUENTE
An the data types fot the fields are:
FEC_CREACION|datetime|no|8|
FEC_REAL|datetime|no|8| |
| |yes|(n/a)|(n/a)|

Id appreciate your help

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/05/2012 :  19:06:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
I am not clear what your query is doing, but it sounds like you are missing an index. What indexes do you have on that table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 01/05/2012 :  20:20:43  Show Profile  Reply with Quote
Thanks for your help, The index fo the table are:
IX_ACA_INCIDENCIA|nonclustered located on INDICES|TIP_INCIDENCIA
IX_ACA_INCIDENCIA_1|nonclustered located on PRIMARY|FEC_CREACION
IX_ACA_INCIDENCIA_FUENTE|nonclustered located on INDICES|FUENTE
PK_ACA_INCIDENCIA|nonclustered, unique, primary key located on INDICES|NUM_INCIDENCIA, FUENTE

And the the columns are so many, but we use in the query are:

NUM_INCIDENCIA|int|no|4|10
FEC_CREACION|datetime|no|8|
FEC_REAL|datetime|no|8|
FEC_HISTORICO|datetime|no|8| |
COD_CAMPANIA|int|no|4|10 |0





Data_located_on_filegroup
DatosNormalesDos

index_name|index_description|index_keys
IX_ACA_INCIDENCIA|nonclustered located on INDICES|TIP_INCIDENCIA
IX_ACA_INCIDENCIA_1|nonclustered located on PRIMARY|FEC_CREACION
IX_ACA_INCIDENCIA_FUENTE|nonclustered located on INDICES|FUENTE
PK_ACA_INCIDENCIA|nonclustered, unique, primary key located on INDICES|NUM_INCIDENCIA, FUENTE
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/05/2012 :  22:12:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why are you looping through this data? Can't you return it all in one result set? Instead of the ORs in there, I'd recommend using a UNION ALL query which should avoid the scan.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 01/10/2012 :  01:32:57  Show Profile  Reply with Quote
Good evening, i modify the query adding UNION ALL into the query and run the query, but the degradation persist and in the otherhand it takes much more time than the former query:

SET NOCOUNT ON
DECLARE @FECHA_INI CHAR(8)
DECLARE @FECHAFIN CHAR(8)
SET @FECHA_INI = '20120103'
SET @FECHAFIN = '20120104'

WHILE @FECHA_INI < @FECHAFIN
BEGIN

SELECT @FECHA_INI,COUNT(1) CANTIDAD
FROM ACA_INCIDENCIA
WHERE (FEC_CREACION >= @FECHA_INI AND FEC_CREACION < @FECHAFIN)
AND FUENTE = 1
UNION ALL

SELECT @FECHA_INI,COUNT(1) CANTIDAD
FROM ACA_INCIDENCIA AI
WHERE (FEC_REAL >= @FECHA_INI AND FEC_REAL < @FECHAFIN)
AND FUENTE = 1

UNION ALL

SELECT @FECHA_INI,COUNT(1) CANTIDAD
FROM ACA_INCIDENCIA AI2
WHERE (FEC_HISTORICO >= @FECHA_INI AND FEC_HISTORICO < @FECHAFIN)
AND FUENTE = 1



END



Its necessary to konw that in this query the only filed that has index is FEC_CREACION:

index_name|index_description|index_keys
IX_ACA_INCIDENCIA|nonclustered located on INDICES|TIP_INCIDENCIA
IX_ACA_INCIDENCIA_1|nonclustered located on PRIMARY|FEC_CREACION
IX_ACA_INCIDENCIA_FUENTE|nonclustered located on INDICES|FUENTE
PK_ACA_INCIDENCIA|nonclustered, unique, primary key located on INDICES|NUM_INCIDENCIA, FUENTE

What should i do,Thanks in advance for your help
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/10/2012 :  04:22:24  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
But.... WHY ARE YOU LOOPING THROUGH THE DATA

Tara -- I don't think you said that loud enough!

You are performing 60 odd SELECT statements. (one for each day). That means you get multiple separate result sets. Generally that's not what you want.

Surely if you just did 1 SELECT statement and then split the results in the calling application. (ORDER BY is your friend here)



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 01/10/2012 :  09:46:39  Show Profile  Reply with Quote
Thanks a lot Charlie, what you meant its not necessary LOOPING THROUGH THE DATA ? -

2nd, how did you calculate "60 odd SELECT statements. (one for each day). That means you get multiple separate result sets"




Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 01/10/2012 :  09:55:26  Show Profile  Visit webfred's Homepage  Reply with Quote
SET @FECHA_INI = '20120103'
SET @FECHAFIN = '20120304'

shows us: not for one day -> from january to march!


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 01/10/2012 :  10:08:11  Show Profile  Reply with Quote
THanks again charlie, you gave me a shed light to develop the query much better
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 01/10/2012 :  10:10:04  Show Profile  Visit webfred's Homepage  Reply with Quote
you can call me Al


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000