OpenXML Identity Insert: query

Showing posts with label query. Show all posts

Friday, March 30, 2012

Optimizing SQL Query performance

Hi,
I have collected SQL Trace events into a trace table by exporting the trace
into a table.
I have a table Trace1 with following columns:
-RowNumber
-ApplicationName
-DatabaseName
.
.
.
-StartTime
-EndTime
Clustered Index on RowNumber Column.
NonClustered Index on StartTime Column.
Trace1 table contains 100,000 of rows.
Now I am firing a query something like
"select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
But above query gives me timeout error for most of the cases. I have
specified timeout value as 60 seconds.
How can I solve this timeout issue?
Do I need to have some other proper indexes, if current indexes are not
proper?
Do I need to increase the timeout value? What is the optimum value of
Timeout in such scenarios? I am expecting that this table is going to grow
to contain atleast 5 crores of rows. So please suggest what strategy should
I adopt?
Thanks,
PushkarIf you have an index on StartTime, this should be the most efficient way to
retrieve the data. I would not expect 100,000 rows to take more than a
moment. Even without an index, I would expect the query to finish in
seconds.
Check your execution plan and see if it is using the index.
You could try regenerating your statistics on this table, which should get
it to use this index.
Post the full DDL of your table including the indexes themselves, just so we
are perfectly clear on what you have.
http://www.aspfaq.com/etiquette.asp?id=5006
I think the likely culprit here is the use of "Select * ". If this is a
very wide table, you may be timing out moving all of that data across the
network. Also, how many rows does your typical date range select? If you
usually return 90,000 rows, that makes a big difference. If every row
contains 1 meg of data (an extreme case, just to illustrate a point), for
example, that would be 90 gigs moving over the network, and would timeout
every time.
Also, what application are you using to run the query?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23aMdJOHaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have collected SQL Trace events into a trace table by exporting the
trace
> into a table.
> I have a table Trace1 with following columns:
> -RowNumber
> -ApplicationName
> -DatabaseName
> .
> .
> .
> -StartTime
> -EndTime
> Clustered Index on RowNumber Column.
> NonClustered Index on StartTime Column.
> Trace1 table contains 100,000 of rows.
> Now I am firing a query something like
> "select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
> But above query gives me timeout error for most of the cases. I have
> specified timeout value as 60 seconds.
> How can I solve this timeout issue?
> Do I need to have some other proper indexes, if current indexes are not
> proper?
> Do I need to increase the timeout value? What is the optimum value of
> Timeout in such scenarios? I am expecting that this table is going to grow
> to contain atleast 5 crores of rows. So please suggest what strategy
should
> I adopt?
> Thanks,
> Pushkar
>
>
>|||Pushkar,
It depends on the relative amount of rows that the query returns.
I would start with adding a (nonclustered) index on StartTime. If the
query returns just a few percent of all rows and tables rows are
relatively wide as compared to the StartTime column, then this index
will probably be used.
If the query returns more than a few percent, or the rows are narrow,
then a nonclustered index on StartTime might be ignored. If this query
is one of the most important queries in your system, then you could
create a clustered index on StartTime (you will need to change the
current clustered index to nonclustered).
HTH,
Gert-Jan
Pushkar wrote:
> Hi,
> I have collected SQL Trace events into a trace table by exporting the trac
e
> into a table.
> I have a table Trace1 with following columns:
> -RowNumber
> -ApplicationName
> -DatabaseName
> .
> .
> .
> -StartTime
> -EndTime
> Clustered Index on RowNumber Column.
> NonClustered Index on StartTime Column.
> Trace1 table contains 100,000 of rows.
> Now I am firing a query something like
> "select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
> But above query gives me timeout error for most of the cases. I have
> specified timeout value as 60 seconds.
> How can I solve this timeout issue?
> Do I need to have some other proper indexes, if current indexes are not
> proper?
> Do I need to increase the timeout value? What is the optimum value of
> Timeout in such scenarios? I am expecting that this table is going to grow
> to contain atleast 5 crores of rows. So please suggest what strategy shoul
d
> I adopt?
> Thanks,
> Pushkar

Optimizing SQL Query

Hello,
I have to run a script which will delete records from 10 tables
based on a certain condition. Instead of having to run the condition 10
times I thought running it once would have better performance and did
something like this
CREATE PROCEDURE testScript AS
set nocount on
declare @.script_table table (row_id int identity(1,1), sid int)
declare @.max int, @.now int, @.result varchar(100)
insert into @.script_table
select sid from Alpha where lname like 'DOWN' and (fname like 'Tom' or
fname like 'Seinfeld')
select @.max = max(row_id) from @.script_table
select @.now=2, @.result=convert(varchar(6), sid) from @.script_table
where row_id=1
while @.max >=@.now
Begin
select @.result = @.result + ',' + convert(varchar(6), sid) from
@.script_table where row_id=@.now
set @.now=@.now + 1;
End
select * from Beta where convert(varchar(5), sid) in ( @.result )
if @.@.error <> 0
BEGIN
ROLLBACK Transaction
RAISERROR ( 'E', 2, 127)
END
...
...
...
but when I run this I dont get any values. Instead when I run this
query I get the output rows
select * from Beta where convert(varchar(5), sid) in (select sid from
Alpha where lname like 'DOWN' and (fname like 'Tom' or fname like
'Seinfeld'))
since @.result has the output from Alpha with a comma delimiter I was
under the impression that this should give me the result but instead I
dont get any rows. Is this because @.result is a varchar? Am I doing
something wrong here? Any help would be great..
Thanks
Khris> Is this because @.result is a varchar?
Yes. You can, however, parse a delimited string and have values returned in
a table, that you can use in your query.
My favourite solution is this one (by Dejan Sarka):
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Thanks ML, I will try that out|||Thanks ML I will try that out
Khris

Optimizing SELECT query

I have one table (tableDemo) with following structure:

TSID bigint (Primary Key)
TskID bigint
Sequence bigint
Version bigint
Frequency varchar(500)
WOID bigint
DateSchedule datetime
TimeStandard real
MeterEstimated real
MeterLast real
Description ntext
CreatedBy bigint
CreatedDate datetime
ModifiedBy bigint
ModifiedDate datetime
Id uniqueidentifier

I have 8000 records in this table. When I fire simple select command (i.e. select * from tableDemo) it takes more than 120 seconds.

Is there any techniques where I can access all these records within 2-3 seconds ?

Regards,

Why do you need every field of every record? Surely you are not going to display them all on one page? If you want to use them for paging, and are using Sql Server 2005, have a look at ROW_NUMBER:

http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
http://msdn2.microsoft.com/en-us/library/ms186734.aspx

|||

Are there any large files stored in any of the rows being returned? i.e. files or larges amounts of text? Returning only 8000 rows shouldnt take very long esp not 120 seconds. Is the instance of sql on your local machine or located someone else?

Tim

Optimizing query with UDF and table vars and IN

Hi, I am trying to optimize this scenario.
I have a query that is returning a list of services. Each service is done
by an employee and for a client. Employees have rights to see only certain
sevices. They can see any service that is done by an employee they have
rights to OR done for a client they have rights to.
CREATE TABLE [Service] (
service_id Int IDENTITY(1,1) NOT NULL,
emp_id Int,
client_id Int)
I have 2 UDFs that return a list of emp_id's they have rights to and a list
of client_id's they have rights to respectively.
CREATE FUNCTION dbo.f_list_emps (@.my_emp_id Int)
RETURNS @.EmpList TABLE (emp_id int not null unique)
AS
BEGIN
// Fill @.EmpList here with a bunch of queries
END
CREATE FUNCTION dbo.f_list_clients (@.my_emp_id Int)
RETURNS @.ClientList TABLE (client_id int not null unique)
AS
BEGIN
// Fill @.ClientList here with a bunch of queries
END
The actual query is built dynamically because it can have about 15 different
parameters passed to it, but a simplified version would look like:
SELECT * FROM Service
WHERE emp_id IN
(SELECT emp_id FROM dbo.f_list_emps(@.my_emp_id))
OR client_id IN
(SELECT client_id FROM dbo.f_list_clients(@.my_emp_id))
I'm looking for a way to optimize this a bit better. I can't join the table
vars directly because of the 'OR', and I don't want to do a UNION of 2
queries each with a separate join because of all the other parameters
involved in the query.
Thanks for any advice,
DaveDavid D Webb (spivey@.nospam.post.com) writes:
> The actual query is built dynamically because it can have about 15
> different parameters passed to it, but a simplified version would look
> like: >
> SELECT * FROM Service
> WHERE emp_id IN
> (SELECT emp_id FROM dbo.f_list_emps(@.my_emp_id))
> OR client_id IN
> (SELECT client_id FROM dbo.f_list_clients(@.my_emp_id))
> I'm looking for a way to optimize this a bit better. I can't join the
> table vars directly because of the 'OR', and I don't want to do a UNION
> of 2 queries each with a separate join because of all the other
> parameters involved in the query.
It's of course impossible to suggest optimizations when I don't see
the tables, and do not the full query.
What I would consider is to insert the data from the table functions
into temp tables. Temp tables have statistics, and since you are running
a dynamic query anyway, you could just as well make use of that statistics.
If you use the UDFs in the query, SQL Server will make some standard
assumptions about what they return.
I would also consider running a UNION of two queries. If you are building
the query dynamically, it should not be much of an issue to repeat the
queries. But you should benchmark whether UNION actually gives an
improvement.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||A simplified query probably won't do.
But here a tip that might be useful: drop the UDF's. If you can rewrite
them as views, then the optimizer can properly optimize the query. When
using UDF's in this fashion you are bound to run into performance
problems as the resultset grows.
Gert-Jan
David D Webb wrote:
> Hi, I am trying to optimize this scenario.
> I have a query that is returning a list of services. Each service is done
> by an employee and for a client. Employees have rights to see only certai
n
> sevices. They can see any service that is done by an employee they have
> rights to OR done for a client they have rights to.
> CREATE TABLE [Service] (
> service_id Int IDENTITY(1,1) NOT NULL,
> emp_id Int,
> client_id Int)
> I have 2 UDFs that return a list of emp_id's they have rights to and a lis
t
> of client_id's they have rights to respectively.
> CREATE FUNCTION dbo.f_list_emps (@.my_emp_id Int)
> RETURNS @.EmpList TABLE (emp_id int not null unique)
> AS
> BEGIN
> // Fill @.EmpList here with a bunch of queries
> END
> CREATE FUNCTION dbo.f_list_clients (@.my_emp_id Int)
> RETURNS @.ClientList TABLE (client_id int not null unique)
> AS
> BEGIN
> // Fill @.ClientList here with a bunch of queries
> END
> The actual query is built dynamically because it can have about 15 differe
nt
> parameters passed to it, but a simplified version would look like:
> SELECT * FROM Service
> WHERE emp_id IN
> (SELECT emp_id FROM dbo.f_list_emps(@.my_emp_id))
> OR client_id IN
> (SELECT client_id FROM dbo.f_list_clients(@.my_emp_id))
> I'm looking for a way to optimize this a bit better. I can't join the tab
le
> vars directly because of the 'OR', and I don't want to do a UNION of 2
> queries each with a separate join because of all the other parameters
> involved in the query.
> Thanks for any advice,
> Dave

Optimizing Query to Run

I'm trying to get a query to run which looks at completed orders that have not had another paid order in 180 days. The database I'm running it against is very large so I can't get it to complete. Where's what I've got:

select Date =cast(cl1.cl_rundate as datetime(102)),count(cl1.cl_recno) as 'Completed Initials', cl1.cl_status as Status from dbo.vw_Completedorders cl1 where cl1.lob_lineofbusiness = 'aaa'
and cl1.cl_rundate > '20060801' and not exists (
select cl2.cl_company from dbo.vw_Paidorders cl2 where
cl2.lob_lineofbusiness = 'aa'and cl2.cl_company = cl1.cl_order and cl2.cl_rundate > '20060101' and datediff(day,cl2.cl_rundate,cl1.cl_rundate) < 180)
group by cl1.cl_status, cl1.cl_rundate

Aragon:

I started to do a mock-up of this; however, I quickly decided that without additional information this was not a good idea. Since this database is "very large" and you "can't get it complete" I think it is a good idea to try to get more information. What I see as critical to this query is:

Avoiding table scans -- especially on the view that is included in the "not exists" condition|||Unfortunately in both Views, there are no indexes available. I do have a table I could use, Orders. In the Orders table I have 5 indexes available. The only one pertainable to my search is the recNo field. Unfortunately RunDate is not an index or it may actually work.

Both views references 2 tables, orders and lineofbussines. LineOfBussiness has 3 columns and no indexes.

The main table to reference, orders, has approximately 29k rows per day for each day since late 2004. CompletedOrders has about 11k per day for the same period and PaidOrders has about 10k per day.

Does this give you enough background?

|||

Try running the Database Tuning Advisor in SQL Server 2005 or Index Tuning Wizard in SQL Server 2000. You need to use a workload or script that contains this query and other common queries/DML statements. DTA/ITW can then recommend additional indexes that will help. It is hard to tell by just looking at your query where the problem might be. Both the objects are views so we don't know what tables are being referenced or how the views are defined. In addition to this, the indexes on the base tables referenced by the views also matter.

Btw, you are using invalid cast specification for datetime "cast(cl1.cl_rundate as datetime(102))". The code will fail if you upgrade your server to SQL Server 2005. The correct way is to do "cast(cl1.cl_rundate as datetime)". And it is also not clear why you need the cast assuming that the column is smalldatetime/datetime.

|||

Aragon:

This is probably enough for a mock-up, but I have no more time today. The big question is that if you can prove that you need the indexes on the tables or the views will you be allowed to add them?

Dave

|||Thanks for help. Unfortunately, what I really need for indexing is the date field. We tried to index on date before and the database had issues. The rebuild of indexes took too long to load. We have to wait for mainframe jobs to run prior to rebuilding so indexing ran into the work day and caused sych issues with other db's.

the date field is ANSI, so that's why I convert it to a datetime.
|||

Got it. Another piece of optimization that will help is to partition the data; sorry for neglecting to mention this in the previous pass. I will see if I can mock this up this morning.

Dave

|||I got it to run using recno to limit the results but it appears to be giving faulty data. I'm having counts of 500 on days when there were only 100 total.
|||

Aragon:

I finally got some time today to look at this again. I was able to load a bunch of mock-up data and look at the results. Below is the schema that I used for my mock-up along with some questions. I targeted my mock-up for about 10,000,000 rows which is 29K records per day times 365 days.

Dave

-- -
-- Mock-up schema?
--
--
-- QUESTIONS:
--
--
-- 1. I am not sure about the "CL_RUNDATE" field. I have modeled this field as
-- CHAR (10) storing date as '2006.05.21'. Exactly what kind of data element
-- is the "CL_RUNDATE" field?
-- 2. I am not sure how the views join the Orders table and the LineOfBusiness table.
-- How are these two table joined.
-- 3. Your query filters based on the field "LOB_LINEOFBUSINESS"; does that field of
-- the view come from the ORDERS table or the LINEOFBUSINESS table?
--
-- 4. In your overview you state that you "look at completed orders that have not had
-- another paid order in 180 days." However, in your query you alias view
-- VW_paidOrders with cl2 and look for orders > '20060101' while aliasing view
-- VW_completedOrders with cl1 and looking for orders > '20060801'. This seems
-- backwards.
-- 5. When you take the date difference DATEDIFF (day, cl2.cl_runDate, cl1.cl_runDate)
-- and this also seems backwards.
--
-- 6. When you join the two views you join the PaidOrders "cl_company" column to the
-- completedOrders "cl_order column. Are these two columns simply two different
-- instances of a representation of a company?
--
-- 7. Another question that I saw has to do with this issue:
--
-- Company A Order 1 Paid 1/15/2006
-- Company A Order 2 Completed 2/2/2006
-- Company A Order 3 Completed 9/9/2006
--
-- Assuming that 4/2 is 180 days ago we have Order 2 that precedes 4/2 and
-- has no subsequent PAID order. Should this cause to the 2/2 record to be
-- included in the report or should that fact that there was a 9/9 COMPLETED
-- order after the 2/2 order suppress the 2/2 order from being included?
--
-- --
-- Comments:
-- --
--
-- At this point I have put together a "working mockup"; however, I still need these
-- issues above discussed. I tried out a mock-up of 10,000,000 rows. This requires
-- 10 GB of disk space to store and about 10 GB of transient log space to run the
-- process to generate the data.
--
-- The index that I created is intended to access target data without the use of any
-- bookmark lookups. When I benchmarked the first set of tests this index reduced
-- logical read from 200000 to 296 -- a very large I/O reduction; this is a good
-- starting point but still doesn't yet prove anything. I am for the moment somewhat
-- optimistic.
--
-- None of this addresses partitioning of data.
--
-- -
--
-- Tables:
--
-- MockOrder
-- LineOfBusiness
--
-- Views:
--
-- vw_CompletedOrders
-- vw_PaidOrders
-- -
create table dbo.mockOrder
( cl_recno integer identity
constraint pk_mockOrder primary key,

lob_code char (4) not null,
cl_status char (2) not null,
cl_rundate char(10) not null,
cl_company char (30) not null,
filler char (730) not null
)
go

create index lobCompany_ndx
on mockOrder (cl_company, lob_code, cl_status, cl_rundate)
go

create table dbo.LineOfBusiness
( lob_code char (4) not null
constraint pk_lineOfBusiness primary key,

lob_lineOfBusiness char (4) not null
)
go

create view dbo.vw_Completedorders
as
select cl_recno,
a.lob_code,
b.lob_lineOfBusiness,
a.cl_status,
cl_rundate,
cl_company as cl_order
from dbo.mockOrder a
inner join dbo.lineOfBusiness b
on a.cl_status = 'C'
and a.lob_code = b.lob_code
go

create view dbo.vw_paidOrders
as
select cl_recno,
a.lob_code,
b.lob_lineOfBusiness,
a.cl_status,
cl_rundate,
cl_company
from dbo.mockOrder a
inner join dbo.lineOfBusiness b
on a.cl_status = 'P'
and a.lob_code = b.lob_code

|||

Sorry, I missed an index:

create index lobStatus_ndx
on mockOrder (lob_code, cl_status, cl_rundate, cl_company)

|||Wow, that's a lot of work you put into it. Thanks. Answers below.

1. I am not sure about the "CL_RUNDATE" field. I have modeled this field as
-- CHAR (10) storing date as '2006.05.21'. Exactly what kind of data element is the "CL_RUNDATE" field?

A: Ansi date format. Ex, 20060930

-- 2. I am not sure how the views join the Orders table and the LineOfBusiness table.
-- How are these two table joined.

A: There is a sort code field in both tables. LineOfBusiness is a grouping of sort codes.

-- 3. Your query filters based on the field "LOB_LINEOFBUSINESS"; does that field of
-- the view come from the ORDERS table or the LINEOFBUSINESS table?
--

A: It originates in the LOB table, but is added to the records in both Views.

-- 4. In your overview you state that you "look at completed orders that have not had
-- another paid order in 180 days." However, in your query you alias view
-- VW_paidOrders with cl2 and look for orders > '20060101' while aliasing view
-- VW_completedOrders with cl1 and looking for orders > '20060801'. This seems
-- backwards.

A: I think I mispoke. I'm looking for NEW orders completed when there hasn't been an OLD order the last 180 days. I did forget, in the above example to include a cl2.cl_rundate >= cl1.cl_rundate clause.

-- 5. When you take the date difference DATEDIFF (day, cl2.cl_runDate, cl1.cl_runDate)
-- and this also seems backwards.
--

A: See above. But it shouldn't matter, should it?

-- 6. When you join the two views you join the PaidOrders "cl_company" column to the
-- completedOrders "cl_order column. Are these two columns simply two different
-- instances of a representation of a company?
--

A: I mistyped. Both are company

-- 7. Another question that I saw has to do with this issue:
--
-- Company A Order 1 Paid 1/15/2006
-- Company A Order 2 Completed 2/2/2006
-- Company A Order 3 Completed 9/9/2006
--
-- Assuming that 4/2 is 180 days ago we have Order 2 that precedes 4/2 and
-- has no subsequent PAID order. Should this cause to the 2/2 record to be
-- included in the report or should that fact that there was a 9/9 COMPLETED
-- order after the 2/2 order suppress the 2/2 order from being included?

A: In the above example I would hope to select orders 1 and 3.
|||

Aragon:

Thanks for the answers. Here are the test cases that I set up. Please look these over and let me which of these test cases is false:

set nocount on
truncate table mockOrder
select convert (varchar (10), dateadd (day, -180, getdate()), 112)
-- Results: 20060406
-- --
-- CASE 1:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 01' 0011 'AAA' 'C' 20060401
--
-- This row is returned because:
-- (1) LOB = 'AAA'
-- (2) Status = 'C'
-- (3) Run Date (20060401) < 20060406
-- (4) There is no subsequent PAID record
-- --
insert into mockOrder values ( 11, 'AAA', 'C', '20060401', 'Company 01', ' ')
-- --
-- CASE 2:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 02' 0021 'AAA' 'C' 20060501
--
-- No rows returned because rundate > 20060406
-- --
insert into mockOrder values ( 21, 'AAA', 'C', '20060501', 'Company 02', ' ')

-- --
-- CASE 3:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 03' 0031 'BBB' 'C' 20060401
--
-- No rows returned because LOB is 'BBB'
-- --
insert into mockOrder values ( 31, 'BBB', 'C', '20060401', 'Company 03', ' ')

-- --
-- CASE 4:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 04' 0041 'AAA' 'C' 20060402
-- 'COMPANY 04' 0042 'AA' 'P' 20060430
--
-- No rows returned; although there is a qualifying 'C' record, there is
-- a subsequent 'P' record that disqualifies the qualifying 'C' record
-- --
insert into mockOrder values ( 41, 'AAA', 'C', '20060402', 'Company 04', ' ')
insert into mockOrder values ( 42, 'AA', 'P', '20060430', 'Company 04', ' ')

-- --
-- CASE 5:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 05' 0051 'AAA' 'C' 20060102
-- 'COMPANY 05' 0052 'AA' 'P' 20060131
-- 'COMPANY 05' 0053 'AAA' 'C' 20060401
--
-- The row for Rec No 53 is returned because:
-- (1) LOB = 'AAA'
-- (2) Status = 'C'
-- (3) Run Date (20060401) < 20060406
-- (4) There is no subsequent PAID record
-- --
insert into mockOrder values ( 51, 'AAA', 'C', '20060102', 'Company 05', ' ')
insert into mockOrder values ( 52, 'AA', 'P', '20060131', 'Company 05', ' ')
insert into mockOrder values ( 53, 'AAA', 'C', '20060401', 'Company 05', ' ')

-- --
-- CASE 6:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 06' 0061 'AAA' 'C' 20060102
-- 'COMPANY 06' 0062 'AA' 'P' 20060131
-- 'COMPANY 06' 0063 'AAA' 'C' 20060501
--
-- No Rows returned; (1) Rec No 61 would otherwise qualify, but Rec NO 62
-- is a subsequent 'P' record and therefore Rec No 61 does not qualify.
-- Rec No 63 does not qualify because Run Date (20060501) > 20060406
-- --
insert into mockOrder values ( 61, 'AAA', 'C', '20060102', 'Company 06', ' ')
insert into mockOrder values ( 62, 'AA', 'P', '20060131', 'Company 06', ' ')
insert into mockOrder values ( 63, 'AAA', 'C', '20060501', 'Company 06', ' ')

-- --
-- CASE 7:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 07' 0071 'AAA' 'C' 20060102
-- 'COMPANY 07' 0072 'AAA' 'C' 20060531
--
-- Rec No 71 is returned because it has no matching 'P' record
-- --
insert into mockOrder values ( 71, 'AAA', 'C', '20060102', 'Company 07', ' ')
insert into mockOrder values ( 73, 'AAA', 'C', '20060531', 'Company 07', ' ')

-- --
-- CASE 8:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 08' 0081 'AAA' 'C' 20060101
-- 'COMPANY 08' 0062 'AA' 'P' 20060901
--
-- Row included; the qualifying 'C' record does not have a matching 'P'
-- record for 180 days.
-- --
insert into mockOrder values ( 81, 'AAA', 'C', '20060101', 'Company 08', ' ')
insert into mockOrder values ( 82, 'AA', 'P', '20060901', 'Company 08', ' ')

-- --
-- CASE 9:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 09' 0091 'AAA' 'C' 20060101
-- 'COMPANY 09' 0091 'AAA' 'C' 20060515
-- 'COMPANY 08' 0093 'AA' 'P' 20060901
--
-- Row included; Rec No 91 qualifies and does not get a 'P' record until
-- more than 180 days later
-- --
insert into mockOrder values ( 91, 'AAA', 'C', '20060102', 'Company 09', ' ')
insert into mockOrder values ( 92, 'AAA', 'C', '20060515', 'Company 09', ' ')
insert into mockOrder values ( 93, 'AA', 'P', '20060901', 'Company 09', ' ')

-- --
-- CASE 10:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 10' 0101 'AAA' 'C' 20060101
-- 'COMPANY 10' 0102 'AAA' 'C' 20060105
-- 'COMPANY 10' 0103 'AAA' 'C' 20060515
-- 'COMPANY 10' 0104 'AA' 'P' 20060901
--
-- 2 Row included; Rec No 91 qualifies and does not get a 'P' record until
-- more than 180 days later
-- --
insert into mockOrder values (101, 'AAA', 'C', '20060102', 'Company 10', ' ')
insert into mockOrder values (102, 'AAA', 'C', '20060105', 'Company 10', ' ')
insert into mockOrder values (103, 'AAA', 'C', '20060515', 'Company 10', ' ')
insert into mockOrder values (104, 'AA', 'P', '20060901', 'Company 10', ' ')

-- --
select cl_company,
cl_recno,
lob_sort_code,
cl_status,
cl_rundate
from mockOrder
order by cl_recno

--
-- cl_company cl_recno lob_sort_code cl_status cl_rundate
-- -- - -
-- Company 01 11 AAA C 20060401
-- Company 02 21 AAA C 20060501
-- Company 03 31 BBB C 20060401
--
-- Company 04 41 AAA C 20060402
-- Company 04 42 AA P 20060430
--
-- Company 05 51 AAA C 20060102
-- Company 05 52 AA P 20060131
-- Company 05 53 AAA C 20060401
--
-- Company 06 61 AAA C 20060102
-- Company 06 62 AA P 20060131
-- Company 06 63 AAA C 20060501
--
-- Company 07 71 AAA C 20060102
-- Company 07 73 AAA C 20060531
--
-- Company 08 81 AAA C 20060101
-- Company 08 82 AA P 20060901
--
-- Company 09 91 AAA C 20060102
-- Company 09 92 AAA C 20060515
-- Company 09 93 AA P 20060901
--
-- Company 10 101 AAA C 20060102
-- Company 10 102 AAA C 20060105
-- Company 10 103 AAA C 20060515
-- Company 10 104 AA P 20060901
--
--

After inserting the test data described above, I ran the following with the indicated results:

declare @.compDate char (8)
declare @.startDate char (8)
declare @.paidDate char (8)
set @.compDate = convert (varchar (8), dateadd (day, -180, getdate()), 112)
set @.startDate = '20060101'
set @.paidDate = convert (varchar (8), dateadd (day, 180, @.startdate), 112)
-- --
-- Display dates that are important to the selection process
-- --
select @.compDate as [@.compDate],
@.startDate as [@.startDate],
@.paidDate as [paidDate]
-- @.compDate @.startDate paidDate
-- - --
-- 20060406 20060101 20060630

-- --
-- Display the records that make it through the filtering process
-- --
select cl_company,
cl_recno,
lob_LineOfBusiness,
cl_rundate
from dbo.vw_completedOrders cmp
where lob_lineOfBusiness = 'AAA'
and cl_rundate >= @.startDate
and cl_rundate <= '20060406'
and not exists
( select pd.cl_company
from dbo.vw_paidOrders pd
where pd.cl_company = cmp.cl_company
and pd.cl_rundate > cmp.cl_rundate
and datediff (day, cmp.cl_rundate, pd.cl_rundate) <= 180
and pd.lob_lineOfBusiness = 'AA'
)
-- cl_company cl_recno lob_LineOfBusiness cl_rundate
-- -- -
-- Company 01 11 aaa 20060401
-- Company 05 53 aaa 20060401
-- Company 07 71 aaa 20060102
-- Company 08 81 aaa 20060101
-- Company 09 91 aaa 20060102
-- Company 10 101 aaa 20060102
-- Company 10 102 aaa 20060105

-- --
-- Display the summary
-- --
select Date = cast (cmp.cl_rundate as datetime (102)),
count(cmp.cl_recno) as 'Completed Initials',
cmp.cl_status as Status
from dbo.vw_completedOrders cmp
where lob_lineOfBusiness = 'AAA'
and cl_rundate >= @.startDate
and cl_rundate <= '20060406'
and not exists
( select pd.cl_company
from dbo.vw_paidOrders pd
where pd.cl_company = cmp.cl_company
and pd.cl_rundate > cmp.cl_rundate
and datediff (day, cmp.cl_rundate, pd.cl_rundate) <= 180
and pd.lob_lineOfBusiness = 'AA'
)
group by cmp.cl_status, cmp.cl_rundate
-- Date Completed Initials Status
--
-- 2006-01-01 00:00:00.000 1 C
-- 2006-01-02 00:00:00.000 3 C
-- 2006-01-05 00:00:00.000 1 C
-- 2006-04-01 00:00:00.000 2 C

Let me know if this looks close.

Dave

Optimizing Query to Run

Aragon:

Both views references 2 tables, orders and lineofbussines. LineOfBussiness has 3 columns and no indexes.

The main table to reference, orders, has approximately 29k rows per day for each day since late 2004. CompletedOrders has about 11k per day for the same period and PaidOrders has about 10k per day.

Does this give you enough background?|||

|||

Aragon:

This is probably enough for a mock-up, but I have no more time today. The big question is that if you can prove that you need the indexes on the tables or the views will you be allowed to add them?

Dave

the date field is ANSI, so that's why I convert it to a datetime.|||

Got it. Another piece of optimization that will help is to partition the data; sorry for neglecting to mention this in the previous pass. I will see if I can mock this up this morning.

Dave

|||I got it to run using recno to limit the results but it appears to be giving faulty data. I'm having counts of 500 on days when there were only 100 total.|||

Aragon:

Dave

lob_code char (4) not null,
cl_status char (2) not null,
cl_rundate char(10) not null,
cl_company char (30) not null,
filler char (730) not null
)
go

create index lobCompany_ndx
on mockOrder (cl_company, lob_code, cl_status, cl_rundate)
go

create table dbo.LineOfBusiness
( lob_code char (4) not null
constraint pk_lineOfBusiness primary key,

lob_lineOfBusiness char (4) not null
)
go

|||

Sorry, I missed an index:

create index lobStatus_ndx
on mockOrder (lob_code, cl_status, cl_rundate, cl_company)

|||Wow, that's a lot of work you put into it. Thanks. Answers below.

-- 2. I am not sure how the views join the Orders table and the LineOfBusiness table.
-- How are these two table joined.

A: There is a sort code field in both tables. LineOfBusiness is a grouping of sort codes.

-- 3. Your query filters based on the field "LOB_LINEOFBUSINESS"; does that field of
-- the view come from the ORDERS table or the LINEOFBUSINESS table?
--

A: It originates in the LOB table, but is added to the records in both Views.

-- 5. When you take the date difference DATEDIFF (day, cl2.cl_runDate, cl1.cl_runDate)
-- and this also seems backwards.
--

A: See above. But it shouldn't matter, should it?

A: I mistyped. Both are company

A: In the above example I would hope to select orders 1 and 3.
|||

Aragon:

Thanks for the answers. Here are the test cases that I set up. Please look these over and let me which of these test cases is false:

set nocount on
truncate table mockOrder
select convert (varchar (10), dateadd (day, -180, getdate()), 112)
-- Results: 20060406
-- --
-- CASE 1:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 01' 0011 'AAA' 'C' 20060401
--
-- This row is returned because:
-- (1) LOB = 'AAA'
-- (2) Status = 'C'
-- (3) Run Date (20060401) < 20060406
-- (4) There is no subsequent PAID record
-- --
insert into mockOrder values ( 11, 'AAA', 'C', '20060401', 'Company 01', ' ')
-- --
-- CASE 2:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 02' 0021 'AAA' 'C' 20060501
--
-- No rows returned because rundate > 20060406
-- --
insert into mockOrder values ( 21, 'AAA', 'C', '20060501', 'Company 02', ' ')

-- --
-- CASE 3:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 03' 0031 'BBB' 'C' 20060401
--
-- No rows returned because LOB is 'BBB'
-- --
insert into mockOrder values ( 31, 'BBB', 'C', '20060401', 'Company 03', ' ')

-- --
-- CASE 4:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 04' 0041 'AAA' 'C' 20060402
-- 'COMPANY 04' 0042 'AA' 'P' 20060430
--
-- No rows returned; although there is a qualifying 'C' record, there is
-- a subsequent 'P' record that disqualifies the qualifying 'C' record
-- --
insert into mockOrder values ( 41, 'AAA', 'C', '20060402', 'Company 04', ' ')
insert into mockOrder values ( 42, 'AA', 'P', '20060430', 'Company 04', ' ')

-- --
-- CASE 5:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 05' 0051 'AAA' 'C' 20060102
-- 'COMPANY 05' 0052 'AA' 'P' 20060131
-- 'COMPANY 05' 0053 'AAA' 'C' 20060401
--
-- The row for Rec No 53 is returned because:
-- (1) LOB = 'AAA'
-- (2) Status = 'C'
-- (3) Run Date (20060401) < 20060406
-- (4) There is no subsequent PAID record
-- --
insert into mockOrder values ( 51, 'AAA', 'C', '20060102', 'Company 05', ' ')
insert into mockOrder values ( 52, 'AA', 'P', '20060131', 'Company 05', ' ')
insert into mockOrder values ( 53, 'AAA', 'C', '20060401', 'Company 05', ' ')

-- --
-- CASE 6:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 06' 0061 'AAA' 'C' 20060102
-- 'COMPANY 06' 0062 'AA' 'P' 20060131
-- 'COMPANY 06' 0063 'AAA' 'C' 20060501
--
-- No Rows returned; (1) Rec No 61 would otherwise qualify, but Rec NO 62
-- is a subsequent 'P' record and therefore Rec No 61 does not qualify.
-- Rec No 63 does not qualify because Run Date (20060501) > 20060406
-- --
insert into mockOrder values ( 61, 'AAA', 'C', '20060102', 'Company 06', ' ')
insert into mockOrder values ( 62, 'AA', 'P', '20060131', 'Company 06', ' ')
insert into mockOrder values ( 63, 'AAA', 'C', '20060501', 'Company 06', ' ')

-- --
-- CASE 7:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 07' 0071 'AAA' 'C' 20060102
-- 'COMPANY 07' 0072 'AAA' 'C' 20060531
--
-- Rec No 71 is returned because it has no matching 'P' record
-- --
insert into mockOrder values ( 71, 'AAA', 'C', '20060102', 'Company 07', ' ')
insert into mockOrder values ( 73, 'AAA', 'C', '20060531', 'Company 07', ' ')

-- --
-- CASE 8:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 08' 0081 'AAA' 'C' 20060101
-- 'COMPANY 08' 0062 'AA' 'P' 20060901
--
-- Row included; the qualifying 'C' record does not have a matching 'P'
-- record for 180 days.
-- --
insert into mockOrder values ( 81, 'AAA', 'C', '20060101', 'Company 08', ' ')
insert into mockOrder values ( 82, 'AA', 'P', '20060901', 'Company 08', ' ')

-- --
-- CASE 9:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 09' 0091 'AAA' 'C' 20060101
-- 'COMPANY 09' 0091 'AAA' 'C' 20060515
-- 'COMPANY 08' 0093 'AA' 'P' 20060901
--
-- Row included; Rec No 91 qualifies and does not get a 'P' record until
-- more than 180 days later
-- --
insert into mockOrder values ( 91, 'AAA', 'C', '20060102', 'Company 09', ' ')
insert into mockOrder values ( 92, 'AAA', 'C', '20060515', 'Company 09', ' ')
insert into mockOrder values ( 93, 'AA', 'P', '20060901', 'Company 09', ' ')

-- --
-- CASE 10:
--
-- -- -- - --
-- Company Rec No LOB Stat Run Date
-- -- -- - --
--
-- 'COMPANY 10' 0101 'AAA' 'C' 20060101
-- 'COMPANY 10' 0102 'AAA' 'C' 20060105
-- 'COMPANY 10' 0103 'AAA' 'C' 20060515
-- 'COMPANY 10' 0104 'AA' 'P' 20060901
--
-- 2 Row included; Rec No 91 qualifies and does not get a 'P' record until
-- more than 180 days later
-- --
insert into mockOrder values (101, 'AAA', 'C', '20060102', 'Company 10', ' ')
insert into mockOrder values (102, 'AAA', 'C', '20060105', 'Company 10', ' ')
insert into mockOrder values (103, 'AAA', 'C', '20060515', 'Company 10', ' ')
insert into mockOrder values (104, 'AA', 'P', '20060901', 'Company 10', ' ')

-- --
select cl_company,
cl_recno,
lob_sort_code,
cl_status,
cl_rundate
from mockOrder
order by cl_recno

--
-- cl_company cl_recno lob_sort_code cl_status cl_rundate
-- -- - -
-- Company 01 11 AAA C 20060401
-- Company 02 21 AAA C 20060501
-- Company 03 31 BBB C 20060401
--
-- Company 04 41 AAA C 20060402
-- Company 04 42 AA P 20060430
--
-- Company 05 51 AAA C 20060102
-- Company 05 52 AA P 20060131
-- Company 05 53 AAA C 20060401
--
-- Company 06 61 AAA C 20060102
-- Company 06 62 AA P 20060131
-- Company 06 63 AAA C 20060501
--
-- Company 07 71 AAA C 20060102
-- Company 07 73 AAA C 20060531
--
-- Company 08 81 AAA C 20060101
-- Company 08 82 AA P 20060901
--
-- Company 09 91 AAA C 20060102
-- Company 09 92 AAA C 20060515
-- Company 09 93 AA P 20060901
--
-- Company 10 101 AAA C 20060102
-- Company 10 102 AAA C 20060105
-- Company 10 103 AAA C 20060515
-- Company 10 104 AA P 20060901
--
--

After inserting the test data described above, I ran the following with the indicated results:

declare @.compDate char (8)
declare @.startDate char (8)
declare @.paidDate char (8)
set @.compDate = convert (varchar (8), dateadd (day, -180, getdate()), 112)
set @.startDate = '20060101'
set @.paidDate = convert (varchar (8), dateadd (day, 180, @.startdate), 112)
-- --
-- Display dates that are important to the selection process
-- --
select @.compDate as [@.compDate],
@.startDate as [@.startDate],
@.paidDate as [paidDate]
-- @.compDate @.startDate paidDate
-- - --
-- 20060406 20060101 20060630

-- --
-- Display the records that make it through the filtering process
-- --
select cl_company,
cl_recno,
lob_LineOfBusiness,
cl_rundate
from dbo.vw_completedOrders cmp
where lob_lineOfBusiness = 'AAA'
and cl_rundate >= @.startDate
and cl_rundate <= '20060406'
and not exists
( select pd.cl_company
from dbo.vw_paidOrders pd
where pd.cl_company = cmp.cl_company
and pd.cl_rundate > cmp.cl_rundate
and datediff (day, cmp.cl_rundate, pd.cl_rundate) <= 180
and pd.lob_lineOfBusiness = 'AA'
)
-- cl_company cl_recno lob_LineOfBusiness cl_rundate
-- -- -
-- Company 01 11 aaa 20060401
-- Company 05 53 aaa 20060401
-- Company 07 71 aaa 20060102
-- Company 08 81 aaa 20060101
-- Company 09 91 aaa 20060102
-- Company 10 101 aaa 20060102
-- Company 10 102 aaa 20060105

-- --
-- Display the summary
-- --
select Date = cast (cmp.cl_rundate as datetime (102)),
count(cmp.cl_recno) as 'Completed Initials',
cmp.cl_status as Status
from dbo.vw_completedOrders cmp
where lob_lineOfBusiness = 'AAA'
and cl_rundate >= @.startDate
and cl_rundate <= '20060406'
and not exists
( select pd.cl_company
from dbo.vw_paidOrders pd
where pd.cl_company = cmp.cl_company
and pd.cl_rundate > cmp.cl_rundate
and datediff (day, cmp.cl_rundate, pd.cl_rundate) <= 180
and pd.lob_lineOfBusiness = 'AA'
)
group by cmp.cl_status, cmp.cl_rundate
-- Date Completed Initials Status
--
-- 2006-01-01 00:00:00.000 1 C
-- 2006-01-02 00:00:00.000 3 C
-- 2006-01-05 00:00:00.000 1 C
-- 2006-04-01 00:00:00.000 2 C

Let me know if this looks close.

Dave

optimizing query performance

Hi,
can I have your ideas about which database structural and physical elements
I would review in order to optimize some query performance?
Thank you,
ManuTop wrote:
> Hi,
> can I have your ideas about which database structural and physical
> elements I would review in order to optimize some query performance?
> Thank you,
Start by looking at the SQL that's running on the server. You can do
this from Profiler using the SQL:BatchCompleted and RPC:Completed
events. Have a look at the CPU, Duration, and Reads columns. Once you
have a good idea what's not running efficiently, you'll know what
objects to look at for tuning.
Imceda offers Coefficient, a SQL performance analysis tool. You can
download a trial from our web site.
David Gugick
Imceda Software
www.imceda.com
sql

Optimizing query execution...

We have SQL Server 2000 and int is an Oracle linked server. I'm trying to run the following query...

SELECT DISTINCT a.auf_nr AS OrderNo,
e.ku_name AS Customer,
d.bestell_dat AS OrdDate,
d.liefer_dat AS DelvDate,
CAST(SUM(b.anz) AS FLOAT) Qty,
CAST(SUM((CAST(c.breite AS FLOAT) / 1000 * CAST(c.hoehe AS FLOAT) / 1000) * b.anz) AS FLOAT) SQM,
CAST(SUM(a.liefer_offen) - (SUM(a.anz) - SUM(b.anz)) AS FLOAT) AvailDelv,
CAST(SUM(a.liefer_anz) AS FLOAT) Delvd,
CAST(SUM(c.sum_brutto*a.anz) AS FLOAT) Value

FROM liorder..LIORDER.AUF_STAT a,
liorder..LIORDER.AUF_LIP_STATUS b,
liorder..LIORDER.AUF_POS c,
liorder..LIORDER.AUF_KOPF d,
liorder..LIORDER.KUST_ADR e

WHERE a.auf_nr = b.auf_nr and
b.auf_nr = c.auf_nr and
c.auf_nr = d.auf_nr and
d.kunr = e.ku_nr and
a.auf_pos = b.auf_pos and
b.auf_pos = c.auf_pos and
b.lip_status = 7 and
c.ver_art !='V' and
a.history = 0 and
a.rg_stat != 2 and
e.ku_name IS not null and
e.ku_vk_ek = 0 and
d.bestell_dat BETWEEN '01/01/2005' and '12/17/2005'

GROUP BY a.auf_nr,
d.liefer_dat,
b.lip_status,
d.bestell_dat,
e.ku_name,
d.kopf_tour,
d.kopf_firma

HAVING CAST(SUM(a.liefer_offen)-(SUM(a.anz)-SUM(b.anz)) AS FLOAT) > 0

..and it takes around 2 minutes to show the results even if the date range is of the same date. I even tried to use an indexed column but I still get the same slow execution time. I even tried to create a UDF so that the WHERE clause would be resolved remotely on the Oracle DB but still the same. Is there anyway I can do it in much more efficient and faster way?I'd use OPENQUERY (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp).

-PatP

Optimizing Query - Product Tips

Hello everyone!
I've got a problem with a real slow query, I would be very happy if somebody has any idea to improve the speed of it...
The idea is to get the top 2 products, a customer hasn't bought wich are in his interest...

query (simplificated)
---------------
SELECT TOP 2 prodID, Title, Price FROM bestSold7Days WHERE
prodID NOT IN (SELECT prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID=394))
AND
(prodType = COALESCE((SELECT TOP 1 products.prodID FROM orders INNER JOIN orderProducts ON order.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID=394) GROUP BY products.prodType ORDER BY SUM(orderProducts.PCS) DESC), 2))
---------------
end query

(COALESCE is for replacing if the customer hasnt ordered anything, or hasnt ordered anything of this type)...

Thanks for any time spent!DDL and sample data would help alot...

But I'll give it a go...

DDL:

CREATE TABLE...

Sample Data

INSERT INTO myTable(Cols...
SELECT 'data','more data',1,...UNION ALL
SELECT 'data','more data',1,...UNION ALL
SELECT 'data','more data',1,...UNION ALL
SELECT 'data','more data',1,...UNION ALL

Should help us get an answer quicker...|||Or maybe not...I think I hurt myself...

Let me point out a coupld of things...

SELECT TOP 2
prodID
, Title
, Price
FROM bestSold7Days
WHERE prodID NOT IN (SELECT prodID
FROM orders
INNER JOIN orderProducts
ON orders.orderID = orderProducts.orderID
WHERE orders.custID=394)
AND (prodType = COALESCE((SELECT TOP 1
products.prodID
FROM orders
INNER JOIN orderProducts ON order.orderID = orderProducts.orderID
INNER JOIN products ON orderProducts.prodID = products.prodID
WHERE orders.custID=394
GROUP BY products.prodType
ORDER BY SUM(orderProducts.PCS) DESC), 2))

Does this even run?

Does ProdType = ProdId?
a GROUP BY qith no SCALAR in the SELECT?
OREDER BY SUM...what for?
Why the COALESCE? IF it's NULL (what ever it is) is won't be evaluated

I guess what I'm saying is..post the ddl sample data AND expected results, and tell us what the business req is...

it'll be a lot faster that way...|||I don't have access to the sql server right now, so I cant give you the result and the table structure 100% - maybe i mistyped something on the query itself, but i dont think so..
I've missed the ORDER by cntSold DESC at the end of the query
it worked as far as i've tried :-)
sorry - let me get some things:

table bestSold7Days (generated hourly, articles best sold in the last 7 days)
prodID - product ID
cntSold - sold pieces in 7 days
prodType - product type - e.g. 0 hardware, 1 software, 2 special product
Title - product Title

table orders
orderID - order ID identity
custID - customer ID

table orderProdcuts (products contained in order)
orderID - order ID
prodID - product ID
pcs - Pieces ordered

the whole query puts out following:
prodID, Title, Price
349, H53-39, 393.33
39392, P3838-3, 5959.21

the sense of the hole thing is to get the top 2 sold products in the last 7 days, wich are the same of interest (prodType) wich the customer prefers and which he didn't already order...|||In your query statement, there are three JOIN words. That makes execution of the query very slow. My suggestion is that you may use a stored procedure in which you can separate your query into several steps. That will improve the performance.|||Hello gyuan,
I've already tried that.
Did a sp wich got me the favorite prodType, but overall it didn't really improve the performance very much.

Currently I'm using it through the stored procedure (4 different tables, 4 different prodTypes) and then querying the top 2 products which he didn't already order.

The SP gives out: 3,4,3,50 - this i split in vb and use it in the queries following...

The whole thing takes from 8-30 seconds (depending on how much the customer ordered)|||What about this?

select top 2 b.prodid
from
BestSold7Days b
left join
(
select distinct(p.prodID)
from Orders o
inner join OrderProducts p
on p.orderid = o.orderid
where o.custID = 394
) x
on x.prodID = b.prodID
where x.prodID is null
order by b.cntSold desc|||A good stored procedure can definitely improve the execution speed of the query statements, but it depends on the content of the stored procedure. If you can post the details of your tables and requirements, that will give us a good help to solve it.|||table sets:
products
products2
products3
products4
(all same structure)
prodID - identity
prodType - product type - int

orders
orderID - order ID (identity)
custID - customer ID - int

orderProducts
orderID - order ID - int
prodID - int
pcs - int - pieces ordered

Here's the sp i currently use to do the query:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION [dbo].[getTopprodType] (@.custID int )
RETURNS varchar(50) AS
BEGIN
DECLARE @.Ret varchar(50)
SET @.Ret=ISNULL((SELECT TOP 1 CONVERT(varchar(50),[products].prodType) FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID = @.custID) GROUP BY products.prodType ORDER BY COUNT(orderProducts.PCS) DESC),'')
SET @.Ret=@.Ret + ',' + ISNULL((SELECT TOP 1 CONVERT(varchar(50),[products2].prodType) FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID INNER JOIN products2 ON orderProducts.prodID = products2.prodID WHERE (orders.custID = @.custID) GROUP BY products2.prodType ORDER BY COUNT(orderProducts.PCS) DESC),'')
SET @.Ret=@.Ret + ',' + ISNULL((SELECT TOP 1 CONVERT(varchar(50),[products3].prodType) FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID INNER JOIN products3 ON orderProducts.prodID = products3.prodID WHERE (orders.custID = @.custID) GROUP BY products3.prodType ORDER BY COUNT(orderProducts.PCS) DESC),'')
SET @.Ret=@.Ret + ',' + ISNULL((SELECT TOP 1 CONVERT(varchar(50),[products4].prodType) FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID INNER JOIN products4 ON orderProducts.prodID = products4.prodID WHERE (orders.custID = @.custID) GROUP BY products4.prodType ORDER BY COUNT(orderProducts.PCS) DESC),'')
RETURN @.Ret
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

There are 4 different product tables (unique id's) wich get together here.
The SP puts out the following
2,3,4,9
I put this into an array in vb to use it for the following query
SELECT TOP 2 bestSold7Days.Anz, products.prodID, products.Title, products.Price FROM bestSold7Days INNER JOIN products ON bestSold7Days.prodID = products.prodID WHERE ((products.Price IS NOT NULL) AND ((SELECT TOP 1 orderProducts.prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID = " & Me.custID & ") GROUP BY orderProducts.prodID HAVING (orderProducts.prodID = bestSold7Days.prodID)) IS NULL)" & tempGen & " ORDER BY bestSold7Days.Anz DESC;

tempGen is replaced by the favourite prodType (e.g. AND prodType=4 if has any)|||found a faster solution for the second query!
if i do the lookup of the products wich the customer already ordered in a derived table (like a join, but the table is a query) it's much faster!
gut the whole process down to ~2-6 seconds.
Now i need to optimize the getTopProdType procedure, still taking some seconds...|||Could you post how many records in each of the tables? And what are indexes on those tables?|||Indexes on all searched fields
prodID
prodType
(in all tables)

products1 ~ 20000 records
products2 ~ 200000 records
products3 ~ 5000 records
products4 ~ 2000000 records
orders ~ 50000 records
orderProducts ~ 2000000 records

prodType is not clustered.
does anybody have a rule when to make an index clustered?|||1. Based on your query, an index on order.custID would be helpful.

2. It would speed up the query a lot if you store customers favorite prodType in a table instead of figuring it out every time you run the query. Daily update on this field might be good enough.|||sorry forgot this field - custID is also indexed.
Only field queried wich is not indexed is the pcs field in the orderProducts table.|||I think item 2 would be very helpful to speed up your query.
If a little slower update on orders related tables is acceptable, you may update the information in table xxx using triggers or whenever an order is updated.

SELECT TOP 2
prodID
, Title
, Price
FROM bestSold7Days
WHERE prodID NOT IN (SELECT prodID
FROM orders
INNER JOIN orderProducts
ON orders.orderID = orderProducts.orderID
WHERE orders.custID=394)
AND (prodType = COALESCE((SELECT prodType
FROM xxxx
WHERE custID=394), 2))|||what do you mean by item 2?
i've just seen that you can only do one clustered index... all main id's are clustered indexes...|||Originally posted by shianmiin
1. Based on your query, an index on order.custID would be helpful.

2. It would speed up the query a lot if you store customers favorite prodType in a table instead of figuring it out every time you run the query. Daily update on this field might be good enough.

I mean the item no. 2 above.|||that query would take 69 hours to finish (~50000 customers, ~5 seconds per prodType get).|||i've just got it working with a runtime of 60 seconds for all customers.
I don't know why it's that fast - but it works :)

INSERT INTO tProdTypeFavourites(custID, prodType1...)
(SELECT ... ) (SELECT ...)

the sub-queries are pretty big but the whole thing runs very fast!|||good job. :)|||thanks - same for you! thnx for every reply!

OPTIMIZING QUERY

I query two fields in my string.. Those are myHour and myCounty. There are
about 5 million records.
myHour , myCountry and cpm fields are indexed. It returns too late..
SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
myHour <= '2006-02-05 23:59:59' and myCountry = 'TR'
when i add myCountry = 'TR' it is getting slower.It takes 45 seconds to
return datas after i run my query.
How can optimize my query...you mean you have three separate indexes - one on myHour, one on myCountry,
and one on cpm column? in that case try with covered index on all three
columns, this should speed things up.
dean
"Savas Ates" <in da club> wrote in message
news:%236XzUo2KGHA.1088@.tk2msftngp13.phx.gbl...
>I query two fields in my string.. Those are myHour and myCounty. There are
> about 5 million records.
> myHour , myCountry and cpm fields are indexed. It returns too late..
> SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
> myHour <= '2006-02-05 23:59:59' and myCountry = 'TR'
> when i add myCountry = 'TR' it is getting slower.It takes 45 seconds
> to
> return datas after i run my query.
>
> How can optimize my query...
>
>|||Without knowing too much about the table structure, and if there are any
clustered indexes, here is what is (probably) happening.
SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
myHour <= '2006-02-05 23:59:59'
would (probably) result in an index s

on the nonclustered index for
myHour, with a bookmark lookup to either the clustered index or table. Resul
t
is returned fairly quick.
Adding the condition for myCountry
SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
myHour <= '2006-02-05 23:59:59' and myCountry = 'TR'
The optimizer sees that either
-the majority of rows in dbilgi have myCoutry = 'TR' , or
-the majority of rows in dbilgi, where myHour is between '2006-02-05
00:00:00' and
'2006-02-05 23:59:59', have a myCountry = 'TR'
and chooses to perform a table scan (or clustered index scan) instead of
using the indexes. Here the optimizer decides that the cost of the bookmark
lookups will be more expensive than just scanning the whole table (or
clustered index).
As Dean mentioned in an earlier reply, you could create a composite index on
myCountry, myHour, and cpm to make the above query faster, but building that
index may take quite a long time on a table with 5 million+ rows.
You could also try using the WITH (INDEX(index_name)) table hint to force
the use of your nonclustered indexes on myCountry and myHour.
"Savas Ates" wrote:

> I query two fields in my string.. Those are myHour and myCounty. There ar
e
> about 5 million records.
> myHour , myCountry and cpm fields are indexed. It returns too late..
> SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
> myHour <= '2006-02-05 23:59:59' and myCountry = 'TR'
> when i add myCountry = 'TR' it is getting slower.It takes 45 seconds
to
> return datas after i run my query.
>
> How can optimize my query...
>
>|||Savas Ates (in da club) writes:
> I query two fields in my string.. Those are myHour and myCounty. There
> are about 5 million records.
> myHour , myCountry and cpm fields are indexed. It returns too late..
> SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
> myHour <= '2006-02-05 23:59:59' and myCountry = 'TR'
> when i add myCountry = 'TR' it is getting slower.It takes 45
> seconds to return datas after i run my query.
Judging from this query alone, a clustered index on myHour could be a
good bet. Or a non-clustered index on (myHour, myCountry, cpm) or
even (myCountry, myHour, cpm). But the latter index would not be
use for the query without myContry.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In addition to what everyone else says. Check out the plan and see what is
happening and what is most costly in each. Do this again after adding
indexes. Figuring out what goes on internally will make this kind of stuff
easier.
One other little point. This value: '2006-02-05 23:59:59' for a date has
two problems.
For smalldatetime:
declare @.date smalldatetime
set @.date = '2006-02-05 23:59:59'
select @.date
Returns:
2006-02-06 00:00:00
declare @.date datetime
set @.date = '2006-02-05 23:59:59.003'
select @.date
select case when @.date <= '2006-02-05 23:59:59' then 1 else 0 end
0
Because of this, your where clause leaves a one second gap. Use '2006-02-05
23:59:59.997' instead (it is only 1 second, but it can make a difference)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Savas Ates" <in da club> wrote in message
news:%236XzUo2KGHA.1088@.tk2msftngp13.phx.gbl...
>I query two fields in my string.. Those are myHour and myCounty. There are
> about 5 million records.
> myHour , myCountry and cpm fields are indexed. It returns too late..
> SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and
> myHour <= '2006-02-05 23:59:59' and myCountry = 'TR'
> when i add myCountry = 'TR' it is getting slower.It takes 45 seconds
> to
> return datas after i run my query.
>
> How can optimize my query...
>
>

Wednesday, March 28, 2012

Optimizing daylight savings query by not using UNION

I was interested in optimizing a query I created in SQL Server 2000 for
adjusting time zones for daylight savings time (only USA DST for simplicity)
.
In essence I have one table "tblDaylightSavingsTime" where the PK is Year,
with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back (ex:
10/30/2005 2:00:00 AM).
I am joining the Daylight Savings Table to another database view, the logic
goes like this:
If the view's datetime falls inside of the DST range for that year, then
adjust for Daylight Savings.
*UNION*
If the view's datetime falls outside of the DST range for that year, then
adjust for Regular Time.
The volume of data pushed through this query is large (in the millions). Is
there any different way to optimize this query by not using UNION?
Thanks!
--
SELECT h.*, DATEADD(hh, h.regularTime, h.indatetime) AS indatetime_adj
FROM qryhsplit_formatted h INNER JOIN
tblDaylightSavingsTime dst ON year(h.indatetime) =
dst.inYear AND ((h.indatetime < dst.Spring_Forward) OR (h.indatetime >
dst.Fall_Back))
UNION ALL
SELECT h.*, DATEADD(hh, h.savingsTime, h.indatetime) AS indatetime_adj
FROM qryhsplit_formatted h INNER JOIN
tblDaylightSavingsTime dst ON year(h.indatetime) =
dst.inYear AND (h.indatetime BETWEEN dst.Spring_Forward AND dst.Fall_Back)How about using a calendar table? This specific example is treated:
http://www.aspfaq.com/2519
"br" <br@.discussions.microsoft.com> wrote in message
news:CA0D1166-D8D5-4C97-8DA1-2E06FDB212B5@.microsoft.com...
>I was interested in optimizing a query I created in SQL Server 2000 for
> adjusting time zones for daylight savings time (only USA DST for
> simplicity).
> In essence I have one table "tblDaylightSavingsTime" where the PK is Year,
> with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back (ex:
> 10/30/2005 2:00:00 AM).
> I am joining the Daylight Savings Table to another database view, the
> logic
> goes like this:
> If the view's datetime falls inside of the DST range for that year, then
> adjust for Daylight Savings.
> *UNION*
> If the view's datetime falls outside of the DST range for that year, then
> adjust for Regular Time.
> The volume of data pushed through this query is large (in the millions).
> Is
> there any different way to optimize this query by not using UNION?
> Thanks!
> --
> SELECT h.*, DATEADD(hh, h.regularTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND ((h.indatetime < dst.Spring_Forward) OR (h.indatetime >
> dst.Fall_Back))
> UNION ALL
> SELECT h.*, DATEADD(hh, h.savingsTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND (h.indatetime BETWEEN dst.Spring_Forward AND dst.Fall_Back)
>|||Try this instead; sorry I couldn't figure out all of your date fields, so I
wrote the below with my own column labels.
SELECT "adjusted_date" =
CASE
WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
THEN DATEADD(hh, 1, t1.[your datetime field])
ELSE t1.[your datetime field]
END
FROM qryhsplit_formatted
INNER JOIN tblDaylightSavingsTime t2
ON YEAR(t1.[your datetime field] = t2.Year
Keep in mind that the dates for DST clock changes will shift starting in 200
7!
"br" wrote:

> I was interested in optimizing a query I created in SQL Server 2000 for
> adjusting time zones for daylight savings time (only USA DST for simplicit
y).
> In essence I have one table "tblDaylightSavingsTime" where the PK is Year
,
> with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back (ex:
> 10/30/2005 2:00:00 AM).
> I am joining the Daylight Savings Table to another database view, the logi
c
> goes like this:
> If the view's datetime falls inside of the DST range for that year, then
> adjust for Daylight Savings.
> *UNION*
> If the view's datetime falls outside of the DST range for that year, then
> adjust for Regular Time.
> The volume of data pushed through this query is large (in the millions).
Is
> there any different way to optimize this query by not using UNION?
> Thanks!
> --
> SELECT h.*, DATEADD(hh, h.regularTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND ((h.indatetime < dst.Spring_Forward) OR (h.indatetime >
> dst.Fall_Back))
> UNION ALL
> SELECT h.*, DATEADD(hh, h.savingsTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND (h.indatetime BETWEEN dst.Spring_Forward AND dst.Fall_Back)
>|||Forgot to put in t1 table alias for the qryhsplit_formatted view, and closin
g
) for YEAR function:
SELECT "adjusted_date" =
CASE
WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
THEN DATEADD(hh, 1, t1.[your datetime field])
ELSE t1.[your datetime field]
END
FROM qryhsplit_formatted t1
INNER JOIN tblDaylightSavingsTime t2
ON YEAR(t1.[your datetime field]) = t2.Year
"Mark Williams" wrote:
> Try this instead; sorry I couldn't figure out all of your date fields, so
I
> wrote the below with my own column labels.
> SELECT "adjusted_date" =
> CASE
> WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
> THEN DATEADD(hh, 1, t1.[your datetime field])
> ELSE t1.[your datetime field]
> END
> FROM qryhsplit_formatted
> INNER JOIN tblDaylightSavingsTime t2
> ON YEAR(t1.[your datetime field] = t2.Year
> Keep in mind that the dates for DST clock changes will shift starting in 2
007!
> --
>
> "br" wrote:
>|||Way faster!! Thanks very much. Sometimes I get bogged down in the syntax
for the Case / If statements, but this makes it much more clear.
Thanks for the heads up on the DST changes for 2007. The constant
historical changes for DST was one of the reasons why I chose to have a
tblDaylightSavingsTime that denotes the start times and end times of DST for
each year.
FYI - my adjustments "h.savingsTime", and "h.regularTime" are Time Zone
specific adjustments for bringing all data elements into Arizona Time - MST
(AZ).
"Mark Williams" wrote:
> Forgot to put in t1 table alias for the qryhsplit_formatted view, and clos
ing
> ) for YEAR function:
> SELECT "adjusted_date" =
> CASE
> WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
> THEN DATEADD(hh, 1, t1.[your datetime field])
> ELSE t1.[your datetime field]
> END
> FROM qryhsplit_formatted t1
> INNER JOIN tblDaylightSavingsTime t2
> ON YEAR(t1.[your datetime field]) = t2.Year
>
> --
>
> "Mark Williams" wrote:
>

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

As i've mentioned that in the BOL when you type "Bitmap Index", you let the user understand how optimized bitmap filtering is implemented. You also demonstrate an example for it comparing both the SQL Server 2005 along with Katmai and in that very example you did mention that the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai when compared to sql Server 2005. Just have a look at the figures.

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

- Costing functions may change between releases. Hence, when you compare query plan costs and operator costs across releases, you are on unstable ground as you make the assumption that the costing function has not been changed.

- The percentages are relative costs. The less expensive the remaining query plan, i.e., the plan on top of the table scan, gets the higher this percentage value will be - assuming that the cost of the scan is the same. If you look into the subtree cost information of the scan, you will see that the cost is the same for both plans. But, the remaining operators, in particular the hash joins on top, have become cheaper since there are fewer rows to process - which explain why you see the percentages increase.

A further note on why the absolute costs between the scan operators remain the same: even with bitmap filters, the table scan operation has to read all the rows from the disk or buffer pool initially. However, as soon as the row is read into the query processor, we can apply the bitmap filter. The main cost, however, is spent in disk or buffer pool reads - the bitmap filter processing itself is very cheap. Therefore, we do not model the bitmap filtering cost in plan generation. However, optimized bitmap filters do model the cardinality changes of the bitmap filter on top of the scan operation.

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||

Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this

sql

Optimizing Clustered Index Scan

I am trying to tune a query that has as its execution output as a Clustered
Index Scan. There is a Clustered Index on field [dt], which is a datetim
e
field. The query is structured as such:
IF @.date < '1/1/1901'
--...then make it NULL, so that it works in the query
SET @.date = NULL
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE ( ( @.date IS NULL )
OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
Clustered Index Seek. Is there any way to rework the WHERE clause to where i
t
would always be a seek?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1Why are you setting @.date to null if @.date is < '1/1/1901'?
MG
"cbrichards" wrote:

> I am trying to tune a query that has as its execution output as a Clustere
d
> Index Scan. There is a Clustered Index on field [dt], which is a datet
ime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where
it
> would always be a seek?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1
>|||Try,
IF @.date < '1/1/1901'
--...then make it NULL, so that it works in the query
SET @.date = NULL
SELECT
t1.dt_UID
FROM
Table1 AS t1
WHERE
t1.dt BETWEEN isnull(@.date, '17530101') AND isnull(DateAdd(d, 1, @.date),
'9999-12-30T23:59:59')
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"cbrichards" wrote:

> I am trying to tune a query that has as its execution output as a Clustere
d
> Index Scan. There is a Clustered Index on field [dt], which is a datet
ime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where
it
> would always be a seek?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1
>|||"cbrichards" <u3288@.uwe> wrote in message news:5f760d81fa01c@.uwe...
>I am trying to tune a query that has as its execution output as a Clustered
> Index Scan. There is a Clustered Index on field [dt], which is a datet
ime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where
> it
> would always be a seek?
>
This is a classic case of pseudo-dynamic SQL. THis is really two different
queries crammed into one. With this query you must do a scan because the
same plan is used whether or not @.date is null. If it is, obviously only a
clustered index scan will do.
IF @.date < '1/1/1901'
BEGIN
SELECT
t1.dt_UID
FROM Table1 AS t1
END
ELSE
BEGIN
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date)
END
david|||David,
Thanks for your response. I ran this in test and (true enough!) the same pla
n
is used whether or not @.date is null. At this point, I am trying to
understand why the same plan is used. Could you explain further, please,
specifically why the same plan is used?
David Browne wrote:
>[quoted text clipped - 14 lines]
>This is a classic case of pseudo-dynamic SQL. THis is really two different
>queries crammed into one. With this query you must do a scan because the
>same plan is used whether or not @.date is null. If it is, obviously only a
>clustered index scan will do.
>IF @.date < '1/1/1901'
>BEGIN
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
>END
>ELSE
>BEGIN
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date)
>END
>david
Message posted via http://www.droptable.com|||"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5f9b39b4a6681@.uwe...
> David,
> Thanks for your response. I ran this in test and (true enough!) the same
> plan
> is used whether or not @.date is null. At this point, I am trying to
> understand why the same plan is used. Could you explain further, please,
> specifically why the same plan is used?
>
Each query gets only one plan. The values you happen to bind to the
parameters don't change that. So whatever the plan is for the query, it has
to work for all possible values of the parameters.
Read:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/pr...005/recomp.mspx
David|||Thanks Alejandro. That appears to resolve the scan regardless of the value o
f
@.date. Much appreciated. Thanks for the link too.
Alejandro Mesa wrote:[vbcol=seagreen]
>Try,
>IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
>SELECT
> t1.dt_UID
>FROM
> Table1 AS t1
>WHERE
> t1.dt BETWEEN isnull(@.date, '17530101') AND isnull(DateAdd(d, 1, @.date),
>'9999-12-30T23:59:59')
>go
>Dynamic Search Conditions in T-SQL
>http://www.sommarskog.se/dyn-search.html
>AMB
>
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com|||David,
I read through the article you provided as a link. I understand better what
you mean by "each query gets only one plan" and "parameters do not change
that."
I am trying to internalize this knowledge and understand it within the
context of my attached statement, which contains the OR condition. As you
originally mentioned, I really have "two different queries crammed into one.
"
As I originally noted, when I remove the ( @.date IS NULL ) portion from the
WHERE clause I get a Clustered Index Seek. Does this mean, that when the pla
n
is created, that in order for the optimizer to cache only one plan (in this
case, using my pseudo dynamic SQL), the optimizer, in essence, will go with
a
plan that will satisfy both conditions, and the plan that satisfies both
conditions is an Index Scan? Is my understanding correct?
David Browne wrote:
>Each query gets only one plan. The values you happen to bind to the
>parameters don't change that. So whatever the plan is for the query, it ha
s
>to work for all possible values of the parameters.
>Read:
>Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 200
5
>http://www.microsoft.com/technet/pr...005/recomp.mspx
>David
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5f9e9495ccc86@.uwe...
> David,
> I read through the article you provided as a link. I understand better
> what
> you mean by "each query gets only one plan" and "parameters do not change
> that."
> I am trying to internalize this knowledge and understand it within the
> context of my attached statement, which contains the OR condition. As you
> originally mentioned, I really have "two different queries crammed into
> one."
> As I originally noted, when I remove the ( @.date IS NULL ) portion from
> the
> WHERE clause I get a Clustered Index Seek. Does this mean, that when the
> plan
> is created, that in order for the optimizer to cache only one plan (in
> this
> case, using my pseudo dynamic SQL), the optimizer, in essence, will go
> with a
> plan that will satisfy both conditions, and the plan that satisfies both
> conditions is an Index Scan? Is my understanding correct?
>
Yes. That is it, exactly.
David|||you would have a LOT better luck if you added anohter variable, and did
the dateadd before the select statement.
The way you wrote it, the system has to calculate the dateadd for each
and every row, and it might not be obvious to the engine how simple
your querty could be.
set @.date2 =DateAdd(d, 1, @.date)
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE ( t1.dt BETWEEN @.date AND @.date2)
OR ( @.date IS NULL )
sounds weird, but sometimes moving the null part around encourages or
discourages the use of hte index. Depends on how that particular engine
parses - you want it to parse and sue the index first.
Worst case you can use a hint to force the engine to use the index.

Friday, March 30, 2012

Optimizing SQL Query performance

Optimizing SQL Query

Optimizing SELECT query

Optimizing query with UDF and table vars and IN

Optimizing Query to Run

Optimizing Query to Run

optimizing query performance

Optimizing query execution...

Optimizing Query - Product Tips

OPTIMIZING QUERY

Wednesday, March 28, 2012

Optimizing daylight savings query by not using UNION

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Optimizing Clustered Index Scan

OpenXML Identity Insert

Blog Archive

About Me