|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Unexpected Execution Planresults are fine but the time to get them are what is causing my concern. My test SQL is the following: SELECT A.NEWCOUNT, B.ACOST FROM IPPCDTL A INNER JOIN IPINVDTL B ON A.IPINVDTLGUID = B.IPINVDTLGUID WHERE A.ORIGONHAND <> B.NEWCOUNT Table IPPCDTL has 150,000 records Table IPINVDTL has 1,500,000 records Looking at the execution plan through Query Analyzer it looks like the following is happening. - It is scanning the index for IPPCDTL and returning only 140 rows out of the 150,000 as per my condition (Cost: 5%) - In parallel is is scanning the index for IPINVDTL and returning 1,500,000 rows (Cost: 71%) - It then creates a hash/inner join that matches up the two scans and returns 140 rows (Cost: 24%) I was surprised by this because I would have thought that SQL Server would reduce the first table to the 140 records and then join the second table to those 140, thus only having to read 140 rows out of the IPINVDTL index instead of all them. I'm not sure why it is reading every single index entry in IPINVDTL. Now of course I could be reading the Execution Plan incorrectly but if I query IPPCDTL alone it comes back in under a second so I wouldn't think that joining a second table for 140 records would make the query time increase to about 40 seconds (Note that it is even slower on my clients computer) Can anyone offer any insights? Many thanks Richard Speiss These are substantially reduced table definitions just to illustrate the execution plan: CREATE TABLE [dbo].[IPPCDTL] ( [IPPCDTLGUID] [uniqueidentifier] NOT NULL , [IPINVDTLGUID] [uniqueidentifier] NULL , [ORIGONHAND] [numeric](14, 4) NULL , [NEWCOUNT] [numeric](14, 4) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[IPPCDTL] ADD CONSTRAINT [PK_IPPCDTL] PRIMARY KEY CLUSTERED ( [IPPCDTLGUID] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Ipinvdtl] ( [IPINVDTLGUID] [uniqueidentifier] NOT NULL , [ACOST] [numeric](13, 4) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Ipinvdtl] ADD CONSTRAINT [PK_Ipinvdtl] PRIMARY KEY CLUSTERED ( [IPINVDTLGUID] ) ON [PRIMARY] GO You'd think the query optimizer would be able to handle this one.
Try INNER LOOP JOIN, instead of INNER JOIN, that should coerce the optimizer to use a loop join. Or you could create an index on IPINVDTLGUID, NEWCOUNT. Show quote "Richard Speiss" <rspe***@mtxinc.com> wrote in message 1,500,000news:epzejowgFHA.2700@TK2MSFTNGP15.phx.gbl... > I am getting strange results when I join two tables together. Actually, the > results are fine but the time to get them are what is causing my concern. > > My test SQL is the following: > > SELECT A.NEWCOUNT, B.ACOST > FROM IPPCDTL A > INNER JOIN IPINVDTL B > ON A.IPINVDTLGUID = B.IPINVDTLGUID > WHERE A.ORIGONHAND <> B.NEWCOUNT > > Table IPPCDTL has 150,000 records > Table IPINVDTL has 1,500,000 records > > Looking at the execution plan through Query Analyzer it looks like the > following is happening. > > - It is scanning the index for IPPCDTL and returning only 140 rows out of > the 150,000 as per my condition (Cost: 5%) > - In parallel is is scanning the index for IPINVDTL and returning Show quote > rows (Cost: 71%) > - It then creates a hash/inner join that matches up the two scans and > returns 140 rows (Cost: 24%) > > I was surprised by this because I would have thought that SQL Server would > reduce the first table to the 140 records and then join the second table to > those 140, thus only having to read 140 rows out of the IPINVDTL index > instead of all them. I'm not sure why it is reading every single index > entry in IPINVDTL. > > Now of course I could be reading the Execution Plan incorrectly but if I > query IPPCDTL alone it comes back in under a second so I wouldn't think that > joining a second table for 140 records would make the query time increase to > about 40 seconds (Note that it is even slower on my clients computer) > > Can anyone offer any insights? > > Many thanks > > Richard Speiss > > These are substantially reduced table definitions just to illustrate the > execution plan: > > CREATE TABLE [dbo].[IPPCDTL] ( > [IPPCDTLGUID] [uniqueidentifier] NOT NULL , > [IPINVDTLGUID] [uniqueidentifier] NULL , > [ORIGONHAND] [numeric](14, 4) NULL , > [NEWCOUNT] [numeric](14, 4) NULL > ) ON [PRIMARY] > GO > > ALTER TABLE [dbo].[IPPCDTL] ADD > CONSTRAINT [PK_IPPCDTL] PRIMARY KEY CLUSTERED > ( > [IPPCDTLGUID] > ) ON [PRIMARY] > GO > > > CREATE TABLE [dbo].[Ipinvdtl] ( > [IPINVDTLGUID] [uniqueidentifier] NOT NULL , > [ACOST] [numeric](13, 4) NULL > ) ON [PRIMARY] > GO > > ALTER TABLE [dbo].[Ipinvdtl] ADD > CONSTRAINT [PK_Ipinvdtl] PRIMARY KEY CLUSTERED > ( > [IPINVDTLGUID] > ) ON [PRIMARY] > GO > > > Hmmm ... the INNER LOOP JOIN actually took longer but I hadn't looked into
the whole HINTs area. Thanks for the idea. I will look further down that road Richard Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:%23vtET0ygFHA.3608@TK2MSFTNGP12.phx.gbl... > You'd think the query optimizer would be able to handle this one. > > Try INNER LOOP JOIN, instead of INNER JOIN, that should coerce the > optimizer > to use a loop join. > > Or you could create an index on IPINVDTLGUID, NEWCOUNT. > > "Richard Speiss" <rspe***@mtxinc.com> wrote in message > news:epzejowgFHA.2700@TK2MSFTNGP15.phx.gbl... >> I am getting strange results when I join two tables together. Actually, > the >> results are fine but the time to get them are what is causing my concern. >> >> My test SQL is the following: >> >> SELECT A.NEWCOUNT, B.ACOST >> FROM IPPCDTL A >> INNER JOIN IPINVDTL B >> ON A.IPINVDTLGUID = B.IPINVDTLGUID >> WHERE A.ORIGONHAND <> B.NEWCOUNT >> >> Table IPPCDTL has 150,000 records >> Table IPINVDTL has 1,500,000 records >> >> Looking at the execution plan through Query Analyzer it looks like the >> following is happening. >> >> - It is scanning the index for IPPCDTL and returning only 140 rows out of >> the 150,000 as per my condition (Cost: 5%) >> - In parallel is is scanning the index for IPINVDTL and returning > 1,500,000 >> rows (Cost: 71%) >> - It then creates a hash/inner join that matches up the two scans and >> returns 140 rows (Cost: 24%) >> >> I was surprised by this because I would have thought that SQL Server >> would >> reduce the first table to the 140 records and then join the second table > to >> those 140, thus only having to read 140 rows out of the IPINVDTL index >> instead of all them. I'm not sure why it is reading every single index >> entry in IPINVDTL. >> >> Now of course I could be reading the Execution Plan incorrectly but if I >> query IPPCDTL alone it comes back in under a second so I wouldn't think > that >> joining a second table for 140 records would make the query time increase > to >> about 40 seconds (Note that it is even slower on my clients computer) >> >> Can anyone offer any insights? >> >> Many thanks >> >> Richard Speiss >> >> These are substantially reduced table definitions just to illustrate the >> execution plan: >> >> CREATE TABLE [dbo].[IPPCDTL] ( >> [IPPCDTLGUID] [uniqueidentifier] NOT NULL , >> [IPINVDTLGUID] [uniqueidentifier] NULL , >> [ORIGONHAND] [numeric](14, 4) NULL , >> [NEWCOUNT] [numeric](14, 4) NULL >> ) ON [PRIMARY] >> GO >> >> ALTER TABLE [dbo].[IPPCDTL] ADD >> CONSTRAINT [PK_IPPCDTL] PRIMARY KEY CLUSTERED >> ( >> [IPPCDTLGUID] >> ) ON [PRIMARY] >> GO >> >> >> CREATE TABLE [dbo].[Ipinvdtl] ( >> [IPINVDTLGUID] [uniqueidentifier] NOT NULL , >> [ACOST] [numeric](13, 4) NULL >> ) ON [PRIMARY] >> GO >> >> ALTER TABLE [dbo].[Ipinvdtl] ADD >> CONSTRAINT [PK_Ipinvdtl] PRIMARY KEY CLUSTERED >> ( >> [IPINVDTLGUID] >> ) ON [PRIMARY] >> GO >> >> >> > > I just noticed that you don't have a foreign key constraint on
IPPCDTL.IPINVDTLGUID. Add one and index the column. Show quote "Richard Speiss" <rspe***@mtxinc.com> wrote in message news:OXvLdN8gFHA.2156@TK2MSFTNGP14.phx.gbl... > Hmmm ... the INNER LOOP JOIN actually took longer but I hadn't looked into > the whole HINTs area. Thanks for the idea. I will look further down that > road > > Richard > > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:%23vtET0ygFHA.3608@TK2MSFTNGP12.phx.gbl... > > You'd think the query optimizer would be able to handle this one. > > > > Try INNER LOOP JOIN, instead of INNER JOIN, that should coerce the > > optimizer > > to use a loop join. > > > > Or you could create an index on IPINVDTLGUID, NEWCOUNT. > > > > "Richard Speiss" <rspe***@mtxinc.com> wrote in message > > news:epzejowgFHA.2700@TK2MSFTNGP15.phx.gbl... > >> I am getting strange results when I join two tables together. Actually, > > the > >> results are fine but the time to get them are what is causing my concern. > >> > >> My test SQL is the following: > >> > >> SELECT A.NEWCOUNT, B.ACOST > >> FROM IPPCDTL A > >> INNER JOIN IPINVDTL B > >> ON A.IPINVDTLGUID = B.IPINVDTLGUID > >> WHERE A.ORIGONHAND <> B.NEWCOUNT > >> > >> Table IPPCDTL has 150,000 records > >> Table IPINVDTL has 1,500,000 records > >> > >> Looking at the execution plan through Query Analyzer it looks like the > >> following is happening. > >> > >> - It is scanning the index for IPPCDTL and returning only 140 rows out of > >> the 150,000 as per my condition (Cost: 5%) > >> - In parallel is is scanning the index for IPINVDTL and returning > > 1,500,000 > >> rows (Cost: 71%) > >> - It then creates a hash/inner join that matches up the two scans and > >> returns 140 rows (Cost: 24%) > >> > >> I was surprised by this because I would have thought that SQL Server > >> would > >> reduce the first table to the 140 records and then join the second table > > to > >> those 140, thus only having to read 140 rows out of the IPINVDTL index > >> instead of all them. I'm not sure why it is reading every single index > >> entry in IPINVDTL. > >> > >> Now of course I could be reading the Execution Plan incorrectly but if I > >> query IPPCDTL alone it comes back in under a second so I wouldn't think > > that > >> joining a second table for 140 records would make the query time increase > > to > >> about 40 seconds (Note that it is even slower on my clients computer) > >> > >> Can anyone offer any insights? > >> > >> Many thanks > >> > >> Richard Speiss > >> > >> These are substantially reduced table definitions just to illustrate the > >> execution plan: > >> > >> CREATE TABLE [dbo].[IPPCDTL] ( > >> [IPPCDTLGUID] [uniqueidentifier] NOT NULL , > >> [IPINVDTLGUID] [uniqueidentifier] NULL , > >> [ORIGONHAND] [numeric](14, 4) NULL , > >> [NEWCOUNT] [numeric](14, 4) NULL > >> ) ON [PRIMARY] > >> GO > >> > >> ALTER TABLE [dbo].[IPPCDTL] ADD > >> CONSTRAINT [PK_IPPCDTL] PRIMARY KEY CLUSTERED > >> ( > >> [IPPCDTLGUID] > >> ) ON [PRIMARY] > >> GO > >> > >> > >> CREATE TABLE [dbo].[Ipinvdtl] ( > >> [IPINVDTLGUID] [uniqueidentifier] NOT NULL , > >> [ACOST] [numeric](13, 4) NULL > >> ) ON [PRIMARY] > >> GO > >> > >> ALTER TABLE [dbo].[Ipinvdtl] ADD > >> CONSTRAINT [PK_Ipinvdtl] PRIMARY KEY CLUSTERED > >> ( > >> [IPINVDTLGUID] > >> ) ON [PRIMARY] > >> GO > >> > >> > >> > > > > > >  |
|||||||||||||||||||||||
Other interesting topics