Prev: Dynamic SQL , Discussion about Erland’s Article
Next: Stored proc permissions
From: gv on 1 Jul 2008 09:42
Hi all,

Using SQL 2000 sp4

This query works, put takes about 11 to 20 seconds.
Just trying to be more effient with it and maybe write it better. I hope I
explain this correctly so you guys can understnd.

--Orders table has 869,000 rows

--Table Schema: (I have left out columns not being used in this query)

CREATE TABLE [ORDERS] (
[OrdersRowID] [int] IDENTITY (1,1) NOT NULL ,
[CustID] [int] NULL ,
[OrderID] [int] NULL ,
[ServiceDescription] [nvarchar] (100) NULL ,
[OrderCreateDate] [datetime] NULL ,
[OrderCloseDate] [datetime] NULL ,
[OrderP] [datetime] NULL ,
[OrderF] [datetime] NULL ,
[OrderPC] [datetime] NULL ,
[OrderStatus] [nvarchar] (50) NULL ,
[OrderAction] [nvarchar] (25) NULL ,
[ItemClass] [nvarchar] (25) NULL ,
[LineServiceDescription] [nvarchar] (50) NULL ,
CONSTRAINT [PK_ORDERS] PRIMARY KEY NONCLUSTERED
(
[OrdersRowID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX IX_ORDERS_CLUS
ON ORDERS
(CustID,OrderID,OrderCreateDate)

The hold up is in the select query in the select list I think? Is there a
better way to do this?
All it's doing is evaluating whether within the order rows of a specific
order does itemclass column contain 'Service' If not then get the
orderAction where
itemclass column = 'Customer'

SELECT A.OrderID,ISNULL(B.ServiceDescription,'(Account Level Order)')as
ServiceDescription,
'OrderAction' = ISNULL((SELECT MAX(OrderAction) from ORDERS WHERE
itemclass = 'Service' and ORDERID = A.OrderID),
(SELECT OrderAction FROM ORDERS WHERE itemclass = 'Customer' and ORDERID
= A.OrderID)),
A.OrderStatus,'ServiceCount' = 0,
A.OrderCreateDate,MAX(A.OrderP)AS OrderP,MAX(A.OrderF)AS
OrderF,A.OrderCloseDate
FROM ORDERS A
LEFT JOIN (SELECT OrderID,LineServiceDescription as
ServiceDescription,OrderAction
FROM ORDERS )B
ON A.OrderID = B.OrderID and B.ServiceDescription is not null
WHERE A.CustID = '10'
GROUP BY
A.OrderID,B.ServiceDescription,A.OrderStatus,A.OrderCreateDate,A.OrderCloseDate
ORDER BY A.OrderID


If I remove this:

'OrderAction' = ISNULL((SELECT MAX(OrderAction) from ORDERS WHERE
itemclass = 'Service' and ORDERID = A.OrderID),
(SELECT OrderAction FROM ORDERS WHERE itemclass = 'Customer' and
ORDERID = A.OrderID)),

takes less than a second

thanks
gv


From: Roy Harvey (SQL Server MVP) on 1 Jul 2008 10:38
Are there any other indexes on the ORDERS table? At a minimum you
would need an index on OrderID for the subqueries in the SELECT list
to work efficiently. As shown, with no such index, each of the
subqueries executed has to perform a complete table scan. Note that
an index on (OrderID, itemclass, OrderAction) might perform a bit
better than one just on OrderID.

Roy Harvey
Beacon Falls, CT

On Tue, 1 Jul 2008 09:42:04 -0400, "gv" <viator.gerry(a)gmail.com>
wrote:

>Hi all,
>
>Using SQL 2000 sp4
>
>This query works, put takes about 11 to 20 seconds.
>Just trying to be more effient with it and maybe write it better. I hope I
>explain this correctly so you guys can understnd.
>
>--Orders table has 869,000 rows
>
>--Table Schema: (I have left out columns not being used in this query)
>
>CREATE TABLE [ORDERS] (
> [OrdersRowID] [int] IDENTITY (1,1) NOT NULL ,
> [CustID] [int] NULL ,
> [OrderID] [int] NULL ,
> [ServiceDescription] [nvarchar] (100) NULL ,
> [OrderCreateDate] [datetime] NULL ,
> [OrderCloseDate] [datetime] NULL ,
> [OrderP] [datetime] NULL ,
> [OrderF] [datetime] NULL ,
> [OrderPC] [datetime] NULL ,
> [OrderStatus] [nvarchar] (50) NULL ,
> [OrderAction] [nvarchar] (25) NULL ,
> [ItemClass] [nvarchar] (25) NULL ,
> [LineServiceDescription] [nvarchar] (50) NULL ,
> CONSTRAINT [PK_ORDERS] PRIMARY KEY NONCLUSTERED
> (
> [OrdersRowID]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>
>CREATE CLUSTERED INDEX IX_ORDERS_CLUS
> ON ORDERS
> (CustID,OrderID,OrderCreateDate)
>
>The hold up is in the select query in the select list I think? Is there a
>better way to do this?
> All it's doing is evaluating whether within the order rows of a specific
>order does itemclass column contain 'Service' If not then get the
>orderAction where
> itemclass column = 'Customer'
>
>SELECT A.OrderID,ISNULL(B.ServiceDescription,'(Account Level Order)')as
>ServiceDescription,
> 'OrderAction' = ISNULL((SELECT MAX(OrderAction) from ORDERS WHERE
>itemclass = 'Service' and ORDERID = A.OrderID),
> (SELECT OrderAction FROM ORDERS WHERE itemclass = 'Customer' and ORDERID
>= A.OrderID)),
> A.OrderStatus,'ServiceCount' = 0,
> A.OrderCreateDate,MAX(A.OrderP)AS OrderP,MAX(A.OrderF)AS
>OrderF,A.OrderCloseDate
> FROM ORDERS A
> LEFT JOIN (SELECT OrderID,LineServiceDescription as
>ServiceDescription,OrderAction
> FROM ORDERS )B
> ON A.OrderID = B.OrderID and B.ServiceDescription is not null
> WHERE A.CustID = '10'
> GROUP BY
>A.OrderID,B.ServiceDescription,A.OrderStatus,A.OrderCreateDate,A.OrderCloseDate
> ORDER BY A.OrderID
>
>
>If I remove this:
>
> 'OrderAction' = ISNULL((SELECT MAX(OrderAction) from ORDERS WHERE
>itemclass = 'Service' and ORDERID = A.OrderID),
> (SELECT OrderAction FROM ORDERS WHERE itemclass = 'Customer' and
>ORDERID = A.OrderID)),
>
>takes less than a second
>
>thanks
>gv
>
From: gv on 1 Jul 2008 17:21
Thank you!!!!

Now it is working under a second with that additional information.

thanks
gv



"Roy Harvey (SQL Server MVP)" <roy_harvey(a)snet.net> wrote in message
news:b6gk64hmjqmigj4nebhshf82rdfpqajpp7(a)4ax.com...
> Are there any other indexes on the ORDERS table? At a minimum you
> would need an index on OrderID for the subqueries in the SELECT list
> to work efficiently. As shown, with no such index, each of the
> subqueries executed has to perform a complete table scan. Note that
> an index on (OrderID, itemclass, OrderAction) might perform a bit
> better than one just on OrderID.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 1 Jul 2008 09:42:04 -0400, "gv" <viator.gerry(a)gmail.com>
> wrote:
>
>>Hi all,
>>
>>Using SQL 2000 sp4
>>
>>This query works, put takes about 11 to 20 seconds.
>>Just trying to be more effient with it and maybe write it better. I hope I
>>explain this correctly so you guys can understnd.
>>
>>--Orders table has 869,000 rows
>>
>>--Table Schema: (I have left out columns not being used in this query)
>>
>>CREATE TABLE [ORDERS] (
>> [OrdersRowID] [int] IDENTITY (1,1) NOT NULL ,
>> [CustID] [int] NULL ,
>> [OrderID] [int] NULL ,
>> [ServiceDescription] [nvarchar] (100) NULL ,
>> [OrderCreateDate] [datetime] NULL ,
>> [OrderCloseDate] [datetime] NULL ,
>> [OrderP] [datetime] NULL ,
>> [OrderF] [datetime] NULL ,
>> [OrderPC] [datetime] NULL ,
>> [OrderStatus] [nvarchar] (50) NULL ,
>> [OrderAction] [nvarchar] (25) NULL ,
>> [ItemClass] [nvarchar] (25) NULL ,
>> [LineServiceDescription] [nvarchar] (50) NULL ,
>> CONSTRAINT [PK_ORDERS] PRIMARY KEY NONCLUSTERED
>> (
>> [OrdersRowID]
>> ) ON [PRIMARY]
>>) ON [PRIMARY]
>>GO
>>
>>CREATE CLUSTERED INDEX IX_ORDERS_CLUS
>> ON ORDERS
>> (CustID,OrderID,OrderCreateDate)
>>
>>The hold up is in the select query in the select list I think? Is there a
>>better way to do this?
>> All it's doing is evaluating whether within the order rows of a
>> specific
>>order does itemclass column contain 'Service' If not then get the
>>orderAction where
>> itemclass column = 'Customer'
>>
>>SELECT A.OrderID,ISNULL(B.ServiceDescription,'(Account Level Order)')as
>>ServiceDescription,
>> 'OrderAction' = ISNULL((SELECT MAX(OrderAction) from ORDERS WHERE
>>itemclass = 'Service' and ORDERID = A.OrderID),
>> (SELECT OrderAction FROM ORDERS WHERE itemclass = 'Customer' and
>> ORDERID
>>= A.OrderID)),
>> A.OrderStatus,'ServiceCount' = 0,
>> A.OrderCreateDate,MAX(A.OrderP)AS OrderP,MAX(A.OrderF)AS
>>OrderF,A.OrderCloseDate
>> FROM ORDERS A
>> LEFT JOIN (SELECT OrderID,LineServiceDescription as
>>ServiceDescription,OrderAction
>> FROM ORDERS )B
>> ON A.OrderID = B.OrderID and B.ServiceDescription is not null
>> WHERE A.CustID = '10'
>> GROUP BY
>>A.OrderID,B.ServiceDescription,A.OrderStatus,A.OrderCreateDate,A.OrderCloseDate
>> ORDER BY A.OrderID
>>
>>
>>If I remove this:
>>
>> 'OrderAction' = ISNULL((SELECT MAX(OrderAction) from ORDERS WHERE
>>itemclass = 'Service' and ORDERID = A.OrderID),
>> (SELECT OrderAction FROM ORDERS WHERE itemclass = 'Customer' and
>>ORDERID = A.OrderID)),
>>
>>takes less than a second
>>
>>thanks
>>gv
>>


 | 
Pages: 1
Prev: Dynamic SQL , Discussion about Erland’s Article
Next: Stored proc permissions