On 5 jun, 16:13, Sybaseguru <col...@[EMAIL PROTECTED]
> wrote:
> 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 (
> > =A0 =A0 ID_CSV =A0 =A0 =A0 int =A0 =A0 =A0 =A0 =A0 =A0NOT NULL,
> > =A0 =A0 PMM_DATETIME datetime =A0 =A0 =A0 NOT NULL,
> > =A0 =A0 MINid =A0 =A0 =A0 =A0varchar(15) =A0 =A0NOT NULL
> > )
>
> > CREATE INDEX IX_TABLA_A_PRUEBA
> > ON TABLA_A_PRUEBA(MINid)
>
> > DECLARE @[EMAIL PROTECTED]
VARCHAR(19),
> > =A0 =A0 =A0 =A0 @[EMAIL PROTECTED]
VARCHAR(19)
>
> > SELECT @[EMAIL PROTECTED]
=3D '2008-05-23 00:00:00',
> > =A0 =A0 =A0 =A0 @[EMAIL PROTECTED]
=3D '2008-05-23 23:59:59'
>
> > INSERT INTO TABLA_A_PRUEBA
> > SELECT ID_CSV,
> > =A0 =A0 =A0 =A0 PMM_DATETIME,
> > =A0 =A0 =A0 =A0 MINid
> > FROM CSV
> > WHERE PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
AND @[EMAIL PROTECTED]
>
> > this takes just 24 seconds to insert 88,877 rows in the table named
> > TABLA_A_PRUEBA, after this i ran this query:
>
> > SELECT COUNT(DISTINCT(A.MINid)) AS 'TOTAL_BAZ'
> > FROM TABLA_A_PRUEBA AS A
> > JOIN usr_mines AS B ON A.MINid =3D B.MIN_id
> > WHERE B.corpo_id =3D '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 OBJECT_ID('dbo.TABLA_A_PRUEBA') IS NOT NULL
> > =A0 =A0 =A0DROP TABLE dbo.TABLA_A_PRUEBA
>
> > CREATE TABLE TABLA_A_PRUEBA (
> > =A0 =A0 ID_CSV =A0 =A0 =A0 int =A0 =A0 =A0 =A0 =A0 =A0NOT NULL,
> > =A0 =A0 PMM_DATETIME datetime =A0 =A0 =A0 NOT NULL,
> > =A0 =A0 MINid =A0 =A0 =A0 =A0varchar(15) =A0 =A0NOT NULL
> > )
>
> > CREATE INDEX IX_TABLA_A_PRUEBA
> > ON TABLA_A_PRUEBA(MINid)
>
> > DECLARE @[EMAIL PROTECTED]
VARCHAR(19),
> > =A0 =A0 =A0 =A0 @[EMAIL PROTECTED]
VARCHAR(19)
>
> > SELECT @[EMAIL PROTECTED]
=3D '2008-05-23 00:00:00',
> > =A0 =A0 =A0 =A0 @[EMAIL PROTECTED]
=3D '2008-05-23 23:59:59'
>
> > INSERT INTO TABLA_A_PRUEBA
> > SELECT ID_CSV,
> > =A0 =A0 =A0 =A0 PMM_DATETIME,
> > =A0 =A0 =A0 =A0 MINid
> > FROM CSV
> > WHERE PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
AND @[EMAIL PROTECTED]
>
> > SELECT COUNT(DISTINCT(A.MINid)) AS 'TOTAL_BAZ'
> > FROM TABLA_A_PRUEBA AS A
> > JOIN usr_mines AS B ON A.MINid =3D B.MIN_id
> > WHERE B.corpo_id =3D '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 COUNT(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 FOR STATEMENT 1 (at line 1).
>
> > =A0 =A0 STEP 1
> > =A0 =A0 =A0 =A0 The type of query is SET OPTION ON.
>
> > ------------------------------------------------------------------
>
> > QUERY PLAN FOR STATEMENT 1 (at line 2).
>
> > =A0 =A0 STEP 1
> > =A0 =A0 =A0 =A0 The type of query is INSERT.
> > =A0 =A0 =A0 =A0 The update mode is direct.
>
> > =A0 =A0 =A0 =A0 FROM TABLE
> > =A0 =A0 =A0 =A0 =A0 =A0 usr_mines
> > =A0 =A0 =A0 =A0 =A0 =A0 B
> > =A0 =A0 =A0 =A0 Nested iteration.
> > =A0 =A0 =A0 =A0 Index : usr_mines_PK
> > =A0 =A0 =A0 =A0 Forward scan.
> > =A0 =A0 =A0 =A0 Positioning by key.
> > =A0 =A0 =A0 =A0 Keys are:
> > =A0 =A0 =A0 =A0 =A0 =A0 corpo_id =A0ASC
> > =A0 =A0 =A0 =A0 Using I/O Size 16 Kbytes for index leaf pages.
> > =A0 =A0 =A0 =A0 With LRU Buffer Replacement Strategy for index leaf
page=
s.
> > =A0 =A0 =A0 =A0 Using I/O Size 16 Kbytes for data pages.
> > =A0 =A0 =A0 =A0 With LRU Buffer Replacement Strategy for data pages.
>
> > =A0 =A0 =A0 =A0 FROM TABLE
> > =A0 =A0 =A0 =A0 =A0 =A0 TABLA_A_PRUEBA
> > =A0 =A0 =A0 =A0 =A0 =A0 A
> > =A0 =A0 =A0 =A0 Nested
> > iteration.
> > =A0 =A0 =A0 =A0 Index :
> > IX_TABLA_A_PRUEBA
> > =A0 =A0 =A0 =A0 Forward
> > scan.
> > =A0 =A0 =A0 =A0 Positioning by
> > key.
> > =A0 =A0 =A0 =A0 Index contains all needed columns. Base table will not
b=
e
> > read.
> > =A0 =A0 =A0 =A0 Keys
> > are:
> > =A0 =A0 =A0 =A0 =A0 =A0 MINid
> > ASC
> > =A0 =A0 =A0 =A0 Using I/O Size 16 Kbytes for index leaf
> > pages.
> > =A0 =A0 =A0 =A0 With LRU Buffer Replacement Strategy for index leaf
> > pages.
> > =A0 =A0 =A0 =A0 TO
> > TABLE
>
> > Worktable1.
>
> > =A0 =A0 STEP
> > 2
> > =A0 =A0 =A0 =A0 The type of query is
> > SELECT.
> > =A0 =A0 =A0 =A0 Evaluate Ungrouped COUNT
> > AGGREGATE.
>
> > =A0 =A0 =A0 =A0 FROM
> > TABLE
>
> > Worktable1.
> > =A0 =A0 =A0 =A0 Nested
> > iteration.
> > =A0 =A0 =A0 =A0 Table
> > Scan.
> > =A0 =A0 =A0 =A0 Forward
> > scan.
> > =A0 =A0 =A0 =A0 Positioning at start of
> > table.
> > =A0 =A0 =A0 =A0 Using I/O Size 16 Kbytes for data
> > pages.
> > =A0 =A0 =A0 =A0 With MRU Buffer Replacement Strategy for data
> > pages.
>
> > =A0 =A0 STEP 3
> > =A0 =A0 =A0 =A0 The type of query is SELECT.- Ocultar texto de la cita
-=
>
> - Mostrar texto de la cita -
My showplan is from an interactive session, and i dont understand well
how traslate the showplan, what can i do with this?
|
