July 15, 2002 at 8:59 am
I have read that derived is a performance hog and i have heard everwhere that temp tables are to be avoided. could someone tell me if these query should be ok or if it is complete crap.
--------------------------------------------
select SC2.recordid,
P.ShortSku,
P.Type,
P.Sizevalue,
P.Colorvalue,
P.WidthValue,
P.Moddate,
InventoryStatus =
CASE
WHEN P.Type <> 'T2' and P.Inventory > 0 THEN
'In Stock'
WHEN P.Type <> 'T2' and P.Inventory < 0 THEN
'On backorder, Ships on' + P.Moddate
WHEN P.TYPE = 'T2' THEN
'In Stock'
WHEN P.Type = 'c2' and Inventory < 0 THEN
'Not Available'
ELSE
'In Stock'
END,
P.Inventory from FSProducts P ,
(
SELECT
NullIf(SubString(',' + SC.SKU + ',' , Tally.ID ,
CharIndex(',' , ',' + SC.SKU + ',' , Tally.ID) - Tally.ID) , '') AS SKU,
sc.recordid
FROM Tally, FSShoppingCart as SC
WHERE Tally.ID <= Len(',' + SC.SKU + ',') AND SubString(',' + SC.SKU + ',' , Tally.ID - 1, 1) = ','
AND CharIndex(',' , ',' + SC.SKU + ',' , Tally.ID) - Tally.ID > 0 )
as SC2
WHERE P.SKU = sc2.sku
GO
-------------------------------------------
thanks
</cm>
July 15, 2002 at 11:54 am
It is hard to say if this is a poor or good query as there is a lot of work especially on the subquery join. Which means usually you will get poor performance.
However if you can give me a bit more info on the table structure and sizes and how they relate I can possibly give you a real answer.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 15, 2002 at 12:55 pm
The biggest problem in this query is the functions in the subquery. They mean you won't be able to get indexes used for these columns. I'd bet you are getting a table scan here.
In addition to the info Antares asked for, can you get a showplan?
Steve Jones
July 15, 2002 at 2:52 pm
We use temp tables and derived tables and the queries and stored procedures that use them run fast. I prefer derived tables as this avoids a bottle neck in the TempDB and the over head of creating the temp table.
I don't believe it is bad to use either one, you just need to make sure that you do enough testing to make sure you won't have any problems and that it runs as fast as you want it to.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
July 15, 2002 at 6:40 pm
I teach our developers to use the temp table initially, helps them visualize and check the results as they go. Once everything works, pretty easy to replace the reference to the table with the select that used to build it. I agree that minimizing tempdb usage is a worthy goal, but it does have its uses.
Andy
July 16, 2002 at 7:23 am
i realize that thE product table is not normalized but we are restricted at the moment to importing daily from an image dB through DTS. we basically have to parse the hell out of the data. the products are on sku level so there is definitely duplicated data(about 10000 records total).
------------------------------------------
CREATE TABLE [FSShoppingCart] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CartID] [nvarchar] (50) NULL ,
[ShortSku] [int] NOT NULL ,
[EDP] [int] NULL ,
[SKU] [varchar] (150) NULL ,
[QTY] [int] NULL ,
[CustomerID] [int] NULL ,
[SourceCodeID] [int] NULL ,
[datecreate] [datetime] NOT NULL CONSTRAINT [DF_FSShoppingCart_datecreate] DEFAULT (getdate()),
CONSTRAINT [PK_FSShoppingCart] PRIMARY KEY CLUSTERED
([RecordID]) ON [PRIMARY]
------------------------------------------
CREATE TABLE [FSProducts] (
[category] [varchar] (2) NOT NULL ,
[shortsku] [varchar] (5) NOT NULL ,
[edp] [int] NOT NULL ,
[type] [varchar] (2) NOT NULL ,
[productname] [varchar] (100) NULL ,
[productlongname] [varchar] (100) NULL ,
[unitprice] [money] NULL ,
[productprice] [money] NOT NULL ,
[description] [varchar] (2000) NULL ,
[moddate] [varchar] (8) NULL ,
[productsize] [varchar] (2) NULL ,
[sizevalue] [varchar] (25) NULL ,
[productcolor] [varchar] (2) NULL ,
[colorvalue] [varchar] (25) NULL ,
[productwidth] [varchar] (2) NULL ,
[widthvalue] [varchar] (25) NULL ,
[sku] [varchar] (25) NULL ,
[inventory] [int] NULL ,
[websort] [int] NULL ,
[createdate] [varchar] (8) NULL ,
CONSTRAINT [PK_FSProducts] PRIMARY KEY CLUSTERED
([edp]) ON [PRIMARY] ,
</cm>
July 18, 2002 at 8:13 am
I also need the tally table. But you big problem will be your subquery. a SET SHOWPLAN_TEXT ON to get the execution plan would be helpfully to give a good idea on that problem.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 22, 2002 at 6:59 am
One rule of thumb I like to use as well is to use a Temp table if the information is going to be used more than once. If it is only going to be used once, then you're probably only adding overhead to create the temp table.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply