ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,316 through 9,330 (of 10,143 total)

RE: Proc is giving poor performance

Here's the relevant statements ripped from the sproc for easy testing:
[font="Courier New"]DECLARE @FromDate SmallDateTime,
   @ToDate SmallDateTime,
   @ProfCode smallint,
   @Branch smallint = 0,
   @WHCode VARCHAR(5) = NULL,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2008 at 7:02 am

#905591
RE: Proc is giving poor performance

GilaMonster (12/1/2008)
KPR (12/1/2008)
sorry to reduce it to one sec.
That may not be possible. You're doing a lot of work in there.
What are these?
dbo.fn_ProfileLegalVechile(@ProfCode,@Branch)
dbo.fn_ProfileWareHouse(@ProfCode,@WHCode)
dbo.fn_ProfileCurrency(@ProfCode,@CCYCode)
I'd agree with Gail here. If two...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2008 at 6:34 am

#905566
RE: Proc is giving poor performance

KPR (12/1/2008)
Hi chris by deploying your change the performance has improved from 1 min 33 secs to 56 sec. Can you pls give me some other suggestions to reduce it...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2008 at 6:20 am

#905557
RE: introducing delay of milliseconds or microseconds in stored procedure

Have a look at WAITFOR in BOL, it's exactly what you need.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2008 at 6:00 am

#905544
RE: Proc is giving poor performance

KPR (12/1/2008)
I have tried like this but after running 2 to 3 times Proc is executing in 1 Min 33 secs.
I'm sure it is - it looks like the original...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2008 at 5:57 am

#905543
RE: Proc is giving poor performance

KPR (12/1/2008)
Earlier Proc was returning 279 rows in 1 min 25 secs for 5 days.
Now it is returning 685 rows in 56 secs.
How are you testing, KPI? If you're amending...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2008 at 5:33 am

#905526
RE: Proc is giving poor performance

KPR (12/1/2008)
Hi Chris,
Thanks for your response. I tried with your scenarios(Select ...into #trades).
...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2008 at 5:07 am

#905503
RE: Top n of each value ?

Madhivanan (12/1/2008)
It must be faster. If not compare the execution paln by applying these
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx
Only the third method would work - and it's exactly the same as the OP's suggestion.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2008 at 4:08 am

#905458
RE: Autoincrement variable

dana_turcanu1981 (12/1/2008)
Hy.
I need a temporary table that loks like this:
Col1
1
2
3
4
5
Can I create it with a select statement? I wouldn't like to use a "for"
Thanks
This is quite quick:
SELECT IDENTITY (INT, 1,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2008 at 4:00 am

#905450
RE: Proc is giving poor performance

Hello
1. This sproc would probably run more quickly if you remove all of the statements relating to transactions - you don't need it.
You're SELECTING data from live tables into a...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2008 at 3:42 am

#905442
RE: Top n of each value ?

Eight seconds for around 12 million (simple) rows looks good to me:
[font="Courier New"]-- Return all colours in a batch, but do not return more than two of the same
SELECT *...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2008 at 3:11 am

#905426
RE: Error,while inserting data into Excel from sql server 2005!!!!!!

bhuvnesh.dogra (12/1/2008)
so i cannot insert that SQL table data into Excel(Placed at local drive)
Am i right ?
If the instance of SQL server which is executing your statement...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2008 at 2:11 am

#905412
RE: stored procedure---finding all tables that contain a specific column

Jeff Moden (11/29/2008)
Heh... not to worry... it's not as bad as that dummy that thought the sum of the differences between two points could be used to determine the closest...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 30, 2008 at 6:06 am

#905231
RE: Using a variable to form part of a table name in stored procedure

rbarryyoung (11/29/2008)
You should use dynamic SQL for this, unless you want to make one stored procedure for every business unit.
If you choose to take the dynamic SQL route, then generally...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 30, 2008 at 3:39 am

#905223
RE: random integer number scalar function

Jeff Moden (11/28/2008)
Chris, I've created a Numbers table with the same column name as you (Number), and I cannot get the code to fail the same way as you have...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 29, 2008 at 3:24 am

#905056

Viewing 15 posts - 9,316 through 9,330 (of 10,143 total)