Feedback to reduce query response time.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Feedback to reduce query response time.

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-02-17 : 23:32:06

Hi all,

I have a table CHSUTemp as shown below. I dont have any primary key field or index.


CREATE TABLE [dbo].[CHSUTemp] (
	[TxnDate] [datetime] NULL ,
	[JobNumber] [varchar] (10) NULL ,
	[ClientID] [varchar] (8) NULL ,
	[StockCode] [varchar] (10) NULL ,
	[StockName] [varchar] (150) NULL ,
	[StockType] [varchar] (4) NULL ,
	[StockCategory] [int] NULL ,
	[StockTypeDesc] [varchar] (30) NULL ,
	[Quantity] [int] NULL ,
	[JobType] [varchar] (1) NULL 
) ON [PRIMARY]

Here are queries that i will be running


SELECT CH.ClientID,CH.TxnDate,Sum(CH.Quantity)  as Qty
FROM CHSUTemp CH
	INNER JOIN JobList JL ON JL.JobNumber=CH.JobNumber
	INNER JOIN StockTypeList STL ON STL.StockType=CH.StockType
	INNER JOIN ClientIDList CL ON CL.ClientID=CH.ClientID
	INNER JOIN StockCategory SC ON SC.StockCategory=CH.StockCategory	
	INNER JOIN JobType JT ON  JT.JobType= CH.JobType
WHERE CH.StockCode LIKE ISNULL(@StockCode,'%')  AND CH.TxnDate>=@SDate AND CH.TxnDate<@EDate 
Group BY CH.ClientID,CH.TxnDate
ORDER BY CH.ClientID,CH.TxnDate

SELECT CH.TxnDate,Sum(CH.Quantity)  as Qty
FROM CHSUTemp CH
	INNER JOIN JobList JL ON JL.JobNumber=CH.JobNumber
	INNER JOIN StockTypeList STL ON STL.StockType=CH.StockType
	INNER JOIN ClientIDList CL ON CL.ClientID=CH.ClientID
	INNER JOIN StockCategory SC ON SC.StockCategory=CH.StockCategory	
	INNER JOIN JobType JT ON  JT.JobType= CH.JobType
WHERE CH.StockCode LIKE ISNULL(@StockCode,'%')  AND CH.TxnDate>=@SDate AND CH.TxnDate<@EDate 
Group BY CH.TxnDate
ORDER BY CH.TxnDate

SELECT CH.ClientID,CH.StockTypeDesc,CH.TxnDate,Sum(Quantity)  as Qty
FROM CHSUTemp CH
	INNER JOIN JobList JL ON JL.JobNumber=CH.JobNumber
	INNER JOIN StockTypeList STL ON STL.StockType=CH.StockType
	INNER JOIN ClientIDList CL ON CL.ClientID=CH.ClientID
	INNER JOIN StockCategory SC ON SC.StockCategory=CH.StockCategory	
	INNER JOIN JobType JT ON  JT.JobType= CH.JobType
WHERE CH.StockCode LIKE ISNULL(@StockCode,'%')  AND CH.TxnDate>=@SDate AND CH.TxnDate<@EDate 
Group BY CH.ClientID,CH.StockTypeDesc,CH.TxnDate
ORDER BY CH.ClientID,CH.StockTypeDesc,CH.TxnDate

SELECT CH.ClientID,CH.StockTypeDesc,CH.StockCode,CH.TxnDate,Sum(CH.Quantity)  as Qty
FROM CHSUTemp CH
	INNER JOIN JobList JL ON JL.JobNumber=CH.JobNumber
	INNER JOIN StockTypeList STL ON STL.StockType=CH.StockType
	INNER JOIN ClientIDList CL ON CL.ClientID=CH.ClientID
	INNER JOIN StockCategory SC ON SC.StockCategory=CH.StockCategory	
	INNER JOIN JobType JT ON  JT.JobType= CH.JobType
WHERE CH.StockCode LIKE ISNULL(@StockCode,'%')  AND CH.TxnDate>=@SDate AND CH.TxnDate<@EDate 
Group BY CH.ClientID,CH.StockTypeDesc,CH.StockCode,CH.TxnDate
ORDER BY CH.ClientID,CH.StockTypeDesc,CH.StockCode,CH.TxnDate

Please tell what i should do to increase speed for these queries.
I mean if i add primary key. will it improve performance while running queries even it is not used anywhere.
Or what indexes i should create on CHSUTemp table so that it will reduce respose time for all the quries.

CHSUTemp has approx. 100000 records.

Thanks

mk_garg

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-18 : 04:18:25

And index on TxnDate might be good (clustered maybe?).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-02-20 : 16:11:24

thanks nr

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources