August 11, 2009 at 9:48 am
Hi everybody,
I've been reading here quite a lot but now I also have a problem which I can't seem to figure out. My query joins on several tables and has several columns in the where clause.
Unfortunately the sql server uses a column in there where clause which is not really good and therefore the execution takes approx 2 minutes.
e.g.
select t1.column1
from table1 <- smal table
inner join table2 on table1.id = table2.id <- huge table
where table1.columnX = ?
and table2.columnX = ?
SQL Server seems to use table2.columnx to filter the results first and then the smaller table with the results.
commenting the table2 out the query takes about 5 seconds.
Anybody an idea how to solve this problem besides fixed execution plans because the where clause can differ depending on the entries in the dialouge.
SQL Server 2005 SP 3
Regards,
Stephan
August 11, 2009 at 10:09 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2009 at 3:35 am
Hi there,
attached the execution plan as well as the DDL. The problem part is the where part with the Leistungskopf tables. In the plan you see that i uses those first.
DDL
-- About 2 Million entries
CREATE TABLE [dbo].[VORGANGSKOPF](
[ID] [int] IDENTITY(1,1) NOT NULL,
[VORGANG] [int] NULL,
[SEQUENZ] [int] NOT NULL,
[TESTBUCHUNG] [bit] NOT NULL,
[STORNO] [bit] NOT NULL,
[CRUISE_BEGIN_DATE] [datetime] NULL,
[CRUISE_OBJECT_ID] [int] NULL,
CONSTRAINT [PK_VORGANGSKOPF] PRIMARY KEY CLUSTERED ([ID]))
ALTER TABLE [dbo].[VORGANGSKOPF] WITH CHECK ADD CONSTRAINT [FK_VORGANGS_REF_17346_OBJ_HEAD] FOREIGN KEY([CRUISE_OBJECT_ID]) REFERENCES [dbo].[OBJ_HEAD] ([ID])
CREATE UNIQUE NONCLUSTERED INDEX [VORG_NR] ON [dbo].[VORGANGSKOPF] ([VORGANG],[SEQUENZ])
-- About 3.5 Million entries
CREATE TABLE [dbo].[VORGANG_TEILNEHMER](
[ID] [int] IDENTITY(1,1) NOT NULL,
[VORGANG] [int] NULL,
[ZEILE] [int] NULL,
[ANREDE] [varchar](20) NULL,
[NAME] [varchar](40) NULL,
[GEBURTSDATUM] [datetime] NULL,
[TEILNEHMER_CODE] [varchar](20) NULL,
CONSTRAINT [PK_VORGANG_TEILNEHMER] PRIMARY KEY CLUSTERED ([ID]))
ALTER TABLE [dbo].[VORGANG_TEILNEHMER] WITH CHECK ADD CONSTRAINT [FK_VORGANG__REF_9561_VORGANGS] FOREIGN KEY([VORGANG]) REFERENCES [dbo].[VORGANGSKOPF] ([ID])
CREATE NONCLUSTERED INDEX [TEIL_KUND_NR] ON [dbo].[VORGANG_TEILNEHMER] ([KUNDEN_NUM_NR],[VORGANG])
-- About 4 Million entries
CREATE TABLE [dbo].[BUCHCODE_TEIL_ZUORD](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TEILNEHMER_ID] [int] NOT NULL,
[BUCHUNGSCODE_ID] [int] NULL,
[LEISTUNGS_ID] [int] NOT NULL,
CONSTRAINT [PK_BUCHCODE_TEIL_ZUORD] PRIMARY KEY CLUSTERED ([ID]))
ALTER TABLE [dbo].[BUCHCODE_TEIL_ZUORD] WITH CHECK
ADD CONSTRAINT [FK_BUCHCODE_REF_29916_BUCHUNGS] FOREIGN KEY([BUCHUNGSCODE_ID]) REFERENCES [dbo].[BUCHUNGSCODE] ([ID])
ADD CONSTRAINT [FK_BUCHCODE_REF_30176_VORGANG_] FOREIGN KEY([TEILNEHMER_ID]) REFERENCES [dbo].[VORGANG_TEILNEHMER] ([ID])
ADD CONSTRAINT [FK_BUCHCODE_REF_30177_LEISTUNG] FOREIGN KEY([LEISTUNGS_ID]) REFERENCES [dbo].[LEISTUNGSKOPF] ([ID])
CREATE NONCLUSTERED INDEX [BUCH_CODE_TEIL_BUCHCODE] ON [dbo].[BUCHCODE_TEIL_ZUORD] ([BUCHUNGSCODE_ID],[ZIMMERNR])
CREATE NONCLUSTERED INDEX [BUCH_CODE_TEIL_LEIST] ON [dbo].[BUCHCODE_TEIL_ZUORD] ([LEISTUNGS_ID],[TEILNEHMER_ID])
CREATE NONCLUSTERED INDEX [BUCH_CODE_TEIL_TEIL] ON [dbo].[BUCHCODE_TEIL_ZUORD] ([TEILNEHMER_ID])
-- About 23.5 Million entries
CREATE TABLE [dbo].[LEISTUNGSKOPF](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ANFORDERUNG_MERLIN] [varchar](4) NULL,
[UNTERBRING_MERLIN_1] [varchar](10) NULL,
[UNTERBRING_MERLIN_2] [varchar](6) NULL,
[ANZAHL_MERLIN] [int] NULL,
[OPTION_OFFEN] [bit] NOT NULL,
[REQUEST_OFFEN] [bit] NOT NULL,
[STORNO_LEISTUNG] [bit] NOT NULL,
[PRODUKTTYP] [varchar](6) NULL,
CONSTRAINT [PK_LEISTUNGSKOPF] PRIMARY KEY CLUSTERED ([ID]))
CREATE NONCLUSTERED INDEX [IDx_PRODUKTTYP] ON [dbo].[LEISTUNGSKOPF] ([PRODUKTTYP],[ANFORDERUNG_MERLIN])
CREATE NONCLUSTERED INDEX [IDx_MERLIN] ON [dbo].[LEISTUNGSKOPF] ([LEISTUNG_MERLIN],[ANFORDERUNG_MERLIN])
-- About 35k entries
CREATE TABLE [dbo].[BUCHUNGSCODE](
[ID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](70) NULL,
[BUCHUNGSCODE] [varchar](17) NOT NULL,
[BESCHREIBUNG] [varchar](200) NULL
CONSTRAINT [PK_BUCHUNGSCODE] PRIMARY KEY CLUSTERED ([ID]))
-- About 150 entries
CREATE TABLE [dbo].[OBJ_HEAD](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BEZEICHNUNG] [varchar](40) NOT NULL,
CONSTRAINT [PK_OBJ_HEAD] PRIMARY KEY CLUSTERED ([ID]))
Query
SELECT"VORGANGSKOPF"."VORGANG", "VORGANG_TEILNEHMER"."NAME", "VORGANG_TEILNEHMER"."ANREDE", "VORGANG_TEILNEHMER"."GEBURTSDATUM",
"VORGANGSKOPF"."SEQUENZ", "LEISTUNGSKOPF"."STORNO_LEISTUNG","LEISTUNGSKOPF"."OPTION_OFFEN","LEISTUNGSKOPF"."REQUEST_OFFEN",
"BUCHUNGSCODE"."BUCHUNGSCODE", "BUCHUNGSCODE"."BESCHREIBUNG", "VORGANG_TEILNEHMER"."ID","VORGANG_TEILNEHMER"."TEILNEHMER_CODE",
"LEISTUNGSKOPF"."ANFORDERUNG_MERLIN", "LEISTUNGSKOPF"."UNTERBRING_MERLIN_1", "LEISTUNGSKOPF"."UNTERBRING_MERLIN_2",
"VORGANG_TEILNEHMER"."ZEILE","BUCHUNGSCODE"."NAME","LEISTUNGSKOPF"."ANZAHL_MERLIN", "VORGANGSKOPF"."ID",
"LEISTUNGSKOPF"."ID","OBJ_HEAD"."BEZEICHNUNG","VORGANGSKOPF"."TESTBUCHUNG", "VORGANGSKOPF"."CRUISE_OBJECT_ID",
"VORGANGSKOPF"."CRUISE_BEGIN_DATE", "VORGANGSKOPF"."STORNO","LEISTUNGSKOPF"."PRODUKTTYP"
FROM "VORGANGSKOPF" "VORGANGSKOPF"
INNER JOIN "VORGANG_TEILNEHMER" "VORGANG_TEILNEHMER" ON "VORGANGSKOPF"."ID"="VORGANG_TEILNEHMER"."VORGANG"
LEFT OUTER JOIN "BUCHCODE_TEIL_ZUORD" "BUCHCODE_TEIL_ZUORD" ON "BUCHCODE_TEIL_ZUORD"."TEILNEHMER_ID"="VORGANG_TEILNEHMER"."ID"
LEFT OUTER JOIN "LEISTUNGSKOPF" "LEISTUNGSKOPF" ON "BUCHCODE_TEIL_ZUORD"."LEISTUNGS_ID"="LEISTUNGSKOPF"."ID"
LEFT OUTER JOIN "OBJ_HEAD" "OBJ_HEAD" ON "VORGANGSKOPF"."CRUISE_OBJECT_ID"="OBJ_HEAD"."ID"
LEFT OUTER JOIN "BUCHUNGSCODE" "BUCHUNGSCODE" ON "BUCHCODE_TEIL_ZUORD"."BUCHUNGSCODE_ID"="BUCHUNGSCODE"."ID"
WHERE"VORGANGSKOPF"."SEQUENZ"=0
AND "VORGANGSKOPF"."TESTBUCHUNG"=0
AND "VORGANGSKOPF"."CRUISE_OBJECT_ID"=9
AND ("VORGANGSKOPF"."CRUISE_BEGIN_DATE">={ts '2009-07-20 00:00:00'} AND "VORGANGSKOPF"."CRUISE_BEGIN_DATE"<{ts '2009-08-08 00:00:00'})
AND "VORGANGSKOPF"."STORNO"=0
AND "LEISTUNGSKOPF"."STORNO_LEISTUNG"=0
AND "LEISTUNGSKOPF"."ANFORDERUNG_MERLIN"='SO'
AND "LEISTUNGSKOPF"."PRODUKTTYP"='GI'
ORDER BY "BUCHUNGSCODE"."BUCHUNGSCODE"
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply