December 30, 2008 at 9:20 am
Hi,
Following query is taking 1 min to execute. Please suggest improvements?
SELECT SUM(COALESCE(DATEDIFF(dd, ST.InDate, ST.OutDate) * NumberofParts, 0)) AS NumberOfSummary
FROM STORE ST
INNER JOIN MACHINE
ON ST.StoreId = Id.MACHINE
WHERE ST.cancelDate IS NULL
STORE Table no. of rows 5911253
MACHINE Table no. of rows 240
December 30, 2008 at 9:29 am
which table does NumberofParts sit in and what indexes are on the tables?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 30, 2008 at 9:43 am
It is in store table & both tables having clustered index.
December 30, 2008 at 9:45 am
which column is the clustered index on and also is the storeId FK to the Id on the machine table or the other way around?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 30, 2008 at 9:54 am
ok I've created this code which you will note does not have clustered indexes on both tables but it still takes sec's to run.
you might need to supply the query plan for us?
CREATE TABLE CTS_Store
(StoreId INT,
InDate DATETIME,
OutDate DATETIME,
CancelDate DATETIME,
NumberofParts INT
)
CREATE TABLE CTS_Machine
(id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
col2 INT)
INSERT INTO CTS_Store
SELECT TOP 5911253
ABS(CHECKSUM(NEWID()))%240 ,
DATEADD(d,ABS(CHECKSUM(NEWID()))%1000 + 1000,GETDATE()) as [in],
DATEADD(d,ABS(CHECKSUM(NEWID()))%1000 + 1000,GETDATE()) as [in],
CASE WHEN ABS(CHECKSUM(NEWID()))%10%3 = 0 THEN NULL ELSE GETDATE() END,
ABS(CHECKSUM(NEWID()))%10 + 10
FROM Tally a,tally b
INSERT INTO CTS_Machine (col2)
SELECT TOP 240
0
FROM Tally a,tally b
SELECT SUM(COALESCE(DATEDIFF(dd, ST.InDate, ST.OutDate) * NumberofParts, 0)) AS NumberOfSummary
FROM CTS_STORE ST
INNER JOIN CTS_MACHINE
ON ST.StoreId = CTS_MACHINE.id
WHERE ST.cancelDate IS NULL
[/Code]
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 30, 2008 at 10:08 am
you might want to check the link in my sig (Help us Help you) as well , it gives good advice and what to supply when making posts.
🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 30, 2008 at 10:28 am
bang725 (12/30/2008)
Hi,Following query is taking 1 min to execute. Please suggest improvements?
Please post table and index definitions.
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
December 30, 2008 at 12:29 pm
here are script for table & now let me know?
CREATE TABLE [dbo].[Store](
[Id] [uniqueidentifier] NOT NULL ,
[IncomingType] [varchar](20) NOT NULL,
[ConfirmationId] [varchar](20) NOT NULL,
[CreationDate] [datetime] NOT NULL ,
[StoreId] [int] NOT NULL,
[CustomerNumber] [varchar](20) NOT NULL,
[Agent1] [varchar](10) NOT NULL ,
[Agent2] [varchar](10) NOT NULL ,
[StoreCode] [varchar](4) NOT NULL,
[LocationId] [varchar](32) NOT NULL ,
[StoreName] [varchar](70) NOT NULL ,
[MatCode] [varchar](50) NOT NULL ,
[InRateType] [varchar](32) NOT NULL ,
[InRateCode] [varchar](10) NOT NULL ,
[FirstIn] [nvarchar](30) NOT NULL ,
[LastIn] [nvarchar](30) NOT NULL ,
[MailType] [nvarchar](64) NOT NULL ,
[StoreDescription] [nvarchar](128) NOT NULL ,
[InDate] [datetime] NOT NULL,
[OutDate] [datetime] NOT NULL,
[NumberOfStore] [int] NOT NULL ,
[NumberOfAssembly] [int] NOT NULL ,
[NumberOfSubAssembly] [int] NOT NULL ,
[TotalValue] [money] NOT NULL ,
[CurrencyCode] [varchar](3) NOT NULL ,
[ExchangeRate] [money] NULL,
[InRatePerItem] [money] NOT NULL ,
[OutRatePerItem] [money] NOT NULL ,
[StoreRoomSupplier] [nvarchar](32) NOT NULL ,
[OutRoomDescription] [nvarchar](128) NOT NULL ,
[InRateCurrency] [varchar](3) NOT NULL ,
[OutItemId] [varchar](20) NOT NULL ,
[OutSearch] [numeric](9, 3) NOT NULL ,
[StoreFound] [int] NOT NULL ,
[InitialDuration] [numeric](9, 3) NOT NULL ,
[OutEnabled] [bit] NOT NULL ,
[OutFound] [int] NOT NULL ,
[FullTime] [numeric](9, 3) NOT NULL ,
[AssemblyName] [nvarchar](64) NOT NULL ,
[ChangeDate] [datetime] NULL,
[CancelDate] [datetime] NULL,
[AdditionalInfo1] [nvarchar](256) NOT NULL ,
[AdditionalInfo2] [nvarchar](256) NOT NULL ,
[AdditionalInfo3] [nvarchar](256) NOT NULL ,
[StoreVersion] [varchar](16) NULL,
[InOutType] [varchar](32) NOT NULL ,
[ItemId] [uniqueidentifier] NULL,
[CancelFee] [money] NOT NULL ,
[CancelId] [varchar](25) NOT NULL ,
[Paid] [bit] NOT NULL ,
[CountryTax] [varchar](16) NOT NULL ,
CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED
(
[RowId] ASC
)ON [PRIMARY]
) ON [PRIMARY]
______________________
CREATE TABLE [dbo].[Material](
[id] [int] IDENTITY(1,1) NOT NULL,
[Material] [nchar](5) NOT NULL,
[Name] [varchar](1024) NULL,
[GroupID] [int] NULL,
CONSTRAINT [PK_Material] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY],
CONSTRAINT [IX_Material] UNIQUE NONCLUSTERED
(
[Material] ASC
)ON [PRIMARY]
) ON [PRIMARY]
__________________________________
December 30, 2008 at 1:28 pm
I think that query will benefit from a nonclustered index on the Store table. Try this for starters
CREATE INDEX idx_Store_CancelDateStoreID ON Store (StoreID, CancelDate, InDate, OutDate, NumberofParts)
It's wider than I would like, but no way around that on SQL 2000.
Without the SUM, how many rows does that query return?
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
December 30, 2008 at 1:47 pm
without sum , it is retruning 4416709 rows
December 31, 2008 at 2:18 am
Hi All,
Gail, stop me if I'm wrong, but could the OP not use an EXISTS in the where clause instead of the join and this would allow the optimizer to do an "semi join"?
Wouldn't this speed up the query as well?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 31, 2008 at 3:02 am
SELECTSUM(st.nos) AS NumberOfSummary
FROMMachine AS m
INNER JOIN(
SELECTStoreID,
DATEDIFF(DAY, InDate, OutDate) * NumberofParts AS nos
FROMStore
WHEREcancelDate IS NULL
AND Indate IS NOT NULL
AND Outdate IS NOT NULL
AND NumberofParts IS NOT NULL
) AS st ON st.StoreID = m.ID
OPTION(KEEP ORDER)
N 56°04'39.16"
E 12°55'05.25"
December 31, 2008 at 7:45 am
Christopher Stobbs (12/31/2008)
Gail, stop me if I'm wrong, but could the OP not use an EXISTS in the where clause instead of the join and this would allow the optimizer to do an "semi join"?Wouldn't this speed up the query as well?
Maybe.
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
December 31, 2008 at 7:57 am
Oh ok I guess it depends on the optimizer then
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 5, 2009 at 1:51 pm
is there any way to create temp table and then see?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply