Another big delay...continue

Hi this is what i am doing now.

first i probe this

CREATE TABLE TABLA_A_PRUEBA (
ID_CSV int NT NULL,
PMM_DATETIME datetime NT NULL,
MINid varchar(15) NT NULL
)

CREATE INDEX IX_TABLA_A_PRUEBA
N TABLA_A_PRUEBA(MINid)

DECLARE @dINIT_DATE VARCHAR(19),
@dEND_DATE VARCHAR(19)

SELECT @dINIT_DATE = '2008-05-23 00:00:00',
@dEND_DATE = '2008-05-23 23:59:59'

INSERT INT TABLA_A_PRUEBA
SELECT ID_CSV,
PMM_DATETIME,
MINid
FRM CSV
WHERE PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE

this takes just 24 seconds to insert 88,877 rows in the table named
TABLA_A_PRUEBA, after this i ran this query:

SELECT ****(DISTINCT(A.MINid)) AS 'TTAL_BAZ'
FRM TABLA_A_PRUEBA AS A
JIN usr_mines AS B N A.MINid = B.MIN_id
WHERE B.corpo_id = 'BAZ'

and this just takes 4 seconds to tell me that the result is 440, my
intention is do this into an sp so i put this 2 querys together like
this:

IF BJECT_ID('dbo.TABLA_A_PRUEBA') IS NT NULL
DRP TABLE dbo.TABLA_A_PRUEBA

CREATE TABLE TABLA_A_PRUEBA (
ID_CSV int NT NULL,
PMM_DATETIME datetime NT NULL,
MINid varchar(15) NT NULL
)

CREATE INDEX IX_TABLA_A_PRUEBA
N TABLA_A_PRUEBA(MINid)

DECLARE @dINIT_DATE VARCHAR(19),
@dEND_DATE VARCHAR(19)

SELECT @dINIT_DATE = '2008-05-23 00:00:00',
@dEND_DATE = '2008-05-23 23:59:59'

INSERT INT TABLA_A_PRUEBA
SELECT ID_CSV,
PMM_DATETIME,
MINid
FRM CSV
WHERE PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE

SELECT ****(DISTINCT(A.MINid)) AS 'TTAL_BAZ'
FRM TABLA_A_PRUEBA AS A
JIN usr_mines AS B N A.MINid = B.MIN_id
WHERE B.corpo_id = 'BAZ' -- 440


and surprise!! this takes more than 2 minutes, and it must be 28
seconds at least, so my biq question is why???
this the query plan for the second query: SELECT ****(DISTINCT(

note: the table usr_mines has 2 columns corpo_id and MIN_id and it has
an index named usr_mines_PK with corpo_id declared.

QUERY PLAN FR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET PTIN N.





QUERY PLAN FR STATEMENT 1 (at line 2).


STEP 1
The type of query is INSERT.
The update mode is direct.

FRM TABLE
usr_mines
B
Nested iteration.
Index : usr_mines_PK
Forward scan.
Positioning by key.
Keys are:
corpo_id ASC
Using I/ Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/ Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FRM TABLE
TABLA_A_PRUEBA
A
Nested
iteration.
Index :
IX_TABLA_A_PRUEBA
Forward
scan.
Positioning by
key.
Index contains all needed columns. Base table will not be
read.
Keys
are:
MINid
ASC
Using I/ Size 16 Kbytes for index leaf
pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
T
TABLE

Worktable1.

STEP
2
The type of query is
SELECT.
Evaluate Ungrouped ****
AGGREGATE.

FRM
TABLE

Worktable1.
Nested
iteration.
Table
Scan.
Forward
scan.
Positioning at start of
table.
Using I/ Size 16 Kbytes for data
pages.
With MRU Buffer Replacement Strategy for data
pages.

STEP 3
The type of query is SELECT.

Is you showplan from an interactive session rather than the stored proc
execution?

I suspect the index IX_TABLA_A_PRUEBA is irrelevant as the varchar column
accounts for over 50% of the row length - try it without an index.






Jorge Reyes wrote:

Hi this is what i am doing now.
>
first i probe this
>
CREATE TABLE TABLA_A_PRUEBA (
ID_CSV int NT NULL,
PMM_DATETIME datetime NT NULL,
MINid varchar(15) NT NULL
)
>
CREATE INDEX IX_TABLA_A_PRUEBA
N TABLA_A_PRUEBA(MINid)
>
DECLARE @dINIT_DATE VARCHAR(19),
@dEND_DATE VARCHAR(19)
>
SELECT @dINIT_DATE = '2008-05-23 00:00:00',
@dEND_DATE = '2008-05-23 23:59:59'
>
INSERT INT TABLA_A_PRUEBA
SELECT ID_CSV,
PMM_DATETIME,
MINid
FRM CSV
WHERE PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE
>
this takes just 24 seconds to insert 88,877 rows in the table named
TABLA_A_PRUEBA, after this i ran this query:
>
SELECT ****(DISTINCT(A.MINid)) AS 'TTAL_BAZ'
FRM TABLA_A_PRUEBA AS A
JIN usr_mines AS B N A.MINid = B.MIN_id
WHERE B.corpo_id = 'BAZ'
>
and this just takes 4 seconds to tell me that the result is 440, my
intention is do this into an sp so i put this 2 querys together like
this:
>
IF BJECT_ID('dbo.TABLA_A_PRUEBA') IS NT NULL
DRP TABLE dbo.TABLA_A_PRUEBA
>
CREATE TABLE TABLA_A_PRUEBA (
ID_CSV int NT NULL,
PMM_DATETIME datetime NT NULL,
MINid varchar(15) NT NULL
)
>
CREATE INDEX IX_TABLA_A_PRUEBA
N TABLA_A_PRUEBA(MINid)
>
DECLARE @dINIT_DATE VARCHAR(19),
@dEND_DATE VARCHAR(19)
>
SELECT @dINIT_DATE = '2008-05-23 00:00:00',
@dEND_DATE = '2008-05-23 23:59:59'
>
INSERT INT TABLA_A_PRUEBA
SELECT ID_CSV,
PMM_DATETIME,
MINid
FRM CSV
WHERE PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE
>
SELECT ****(DISTINCT(A.MINid)) AS 'TTAL_BAZ'
FRM TABLA_A_PRUEBA AS A
JIN usr_mines AS B N A.MINid = B.MIN_id
WHERE B.corpo_id = 'BAZ' -- 440
>
>
and surprise!! this takes more than 2 minutes, and it must be 28
seconds at least, so my biq question is why???
this the query plan for the second query: SELECT ****(DISTINCT(
>
note: the table usr_mines has 2 columns corpo_id and MIN_id and it has
an index named usr_mines_PK with corpo_id declared.
>
QUERY PLAN FR STATEMENT 1 (at line 1).
>
>
STEP 1
The type of query is SET PTIN N.
>
>
>

>
QUERY PLAN FR STATEMENT 1 (at line 2).
>
>
STEP 1
The type of query is INSERT.
The update mode is direct.
>
FRM TABLE
usr_mines
B
Nested iteration.
Index : usr_mines_PK
Forward scan.
Positioning by key.
Keys are:
corpo_id ASC
Using I/ Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/ Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
>
FRM TABLE
TABLA_A_PRUEBA
A
Nested
iteration.
Index :
IX_TABLA_A_PRUEBA
Forward
scan.
Positioning by
key.
Index contains all needed columns. Base table will not be
read.
Keys
are:
MINid
ASC
Using I/ Size 16 Kbytes for index leaf
pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
T
TABLE
>
Worktable1.
>
STEP
2
The type of query is
SELECT.
Evaluate Ungrouped ****
AGGREGATE.
>
FRM
TABLE
>
Worktable1.
Nested
iteration.
Table
Scan.
Forward
scan.
Positioning at start of
table.
Using I/ Size 16 Kbytes for data
pages.
With MRU Buffer Replacement Strategy for data
pages.
>
STEP 3
The type of query is SELECT.