Hi,
Can any one give me an idea how can i increase performance of the stored procedure.
In SP many temporary tables are used.
Also i need a information from any one you that is there any tool to find out the performance of a query or SP etc.
Thanks
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
Hi,
I am writing an ASP based application that creates a dynamic querry and then
executes it and displays results. I was thinking about writing a stored procedure to increase performance. How much can the SP help me boost querry responce time ???
Thanks for your time,
Robert
I am trying to test the connection pooling of my app and need to artificially increase the execution of a stored procedure to 10-15 seconds. There aren't any big tables in my sample data to select on. What would be the easiest way to make a stored procedure fun for a few seconds?
Thanks.
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
Hi,
I created a view on two huge tables. I tried to run a simple SELECT statement on this view and it took me several hours to obtain the result. How can I improve the performance of a view? The view should make use of the indexes built in both table, am I right? Thanks.
"Cursor provide row-by-row level processing and it will store the result sets in 'TEMPDB' database".
(Because of this) or (By using Cursor in Triggers or Stored Procedures) the performance will increase or performance will come down?. I am thankful if I get a good reason for this?
Srinivasan
Hello again,
I'll just throw my question: how could I increase SSIS-performance?
I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.
Any ideas?
Greets,
Tom
WHILE (@MyLoop3 > 0)
BEGIN
SELECT Top 1 @UploadId = UploadId,@FirstName = (CASE WHEN FirstName = '' THEN @Update ELSE FirstName END),
@LastName = (CASE WHEN LastName = '' THEN @Update ELSE LastName END),
@Claim = (CASE WHEN Claim = '' THEN @Update ELSE Claim END),
@Insurer = (CASE WHEN Insurer = '' THEN @Update ELSE Insurer END),
@InsurerBranch = (CASE WHEN InsurerBranch = '' THEN @Update ELSE InsurerBranch END),
[Code] .....
Hello,
I have 3 tables (A, B, C) with milions of records (A ca 5 milions, B and C ca 10 milions).
I have created a join betwenn them
select some fields (A, B, C)
FROM
A as a
JOIN
B as B
on
a.a1 = b.a1
and
a.a2 = b.a2
JOIN
C as c
ON
b.b1 = c.b1
and
b.b2 = c.b2
Where fieldtime <= date/time
But it takes to much time: aftre 2 hours and half is still running.
Do you know how to increase the performance?
Thank
Dear all,
I'm using SQL Server 2005 Standard Edetion.
I have the following stored procedure that is executed against two tables (RecrodedCalls) and (RecordedCallsTags)
The table RecordedCalls has more than 10000000 Records and RecordedCallsTags is about 7500000 Records
Now the lines marked in baby blue are dynamic (Dynamic where statement) that varies every time this stored procedure is executed, may it contains 7 columns in condetion statement or may it contains 10 columns, or 2 coulmns.....etc
Now I want to create non-clustered indexes on the columns used in the where statement, THE DTA suggests different indexing whenever the where statement changes.
So what is the right way to created indexes, to create one index on all the columns once, or to create separate indexes on each columns, sometimes the DTA suggests 5 columns together at one if I€™m using 5 conditions, I can€™t accumulate all the possible indexes hence the where statement always vary from situation to situation, below the SP:
CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))
CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))
INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC
INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType
FROM RecordedCalls LEFT OUTER JOIN RecordedCallsTags ON RecordedCalls.ID = RecordedCallsTags.CallID
WHERE RecordedCalls.ID <= '9369907'
AND (RecordedCalls.CallDate BETWEEN cast ('01 Jan 1910 00:00:00:000' as datetime ) AND cast ( '01 Jan 2210 00:00:00:000' as datetime ))
AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)
AND RecordedCalls.ChannelID NOT IN('62061','62062','62063','62064','64110','64111','64112','64113','64114','69860','69861','69862','69863','69866','69867','69868')
AND RecordedCalls.ServerID NOT IN('2')
AND RecordedCalls.AgentID NOT IN('1000010000')
AND (RecordedCallsTags.TagID is null OR RecordedCallsTags.TagID NOT IN('100','200'))
AND RecordedCalls.IsDeleted='false'
GROUP BY RecordedCalls.CallType
SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')
WHEN 'Arabic' THEN #tempLookups.NameA
ELSE #tempLookups.NameE
END AS CallsType FROM
#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code
DROP TABLE #tempLookups
DROP TABLE #tempTable
Thanks all,
Tayseer
Any suggestions how to create efficient indexes??!!
Hey Guys,
I have a question..
i have created a procedure that is about 500 line long.
now this is actually a controller procedure which calles other procedures and functions to generate data for a report.
But this procedure table about 3 min to generate result set. I am not using any temp table. I am using table variables.
My procedure do not recompile.
My rocedure have some insert into ... Exec statements also..
My question is Will performance increase if i split the stored procedure into 2 or 3 or 4 parts?
Hello,
Given that a stored procedure and T-SQL code in query analyzer are exactly the same, why would the stored procedure run much slower?
When I mean much slower I mean 3 sec for the code in query analyzer as opposed to 2:33 sec for the stored procedure.
Exact same code!
Profiler also gives more reads and writes for stored procedure, and a lot of BatchStarted and BatchCompleted directives between the 'start' and 'end' of the stored procedure.
Any help is greatly appreciated.
-Tim
hi
how should i monitor performjance of stored procedure and sql statements. i want to know that how much cpu time a query or stored procedure is taking??
r there any system table which give these information
please give suggestion ASAP
thanx
Maneesh
My application fetches a batch of data through a web service and writes 1000entities per batch to a SQL Server 2000 database. There are 4 tables inevery batch. There are the following number of SQL commands executed peraverage of every batch;Table #1: always 1Table #2: 5Table #3: 5Table #4: 3The problem is that the performance slows down for every batch. Below is anexcerpt from my log file;2004-12-15 12:00:01 Starting job... (RAM usage: 6,38 mb)2004-12-15 12:00:39 data fetch time: 00:00:28 (RAM usage: 23,04 mb)2004-12-15 12:00:39 Total data fetch time: 00:00:37 (RAM usage: 23,04 mb)2004-12-15 12:00:39 Inserting/updating 1000 entities...2004-12-15 12:01:20 Write SQL time: 00:00:402004-12-15 12:01:49 data fetch time: 00:00:24 (RAM usage: 26,87 mb)2004-12-15 12:01:49 Total data fetch time: 00:00:29 (RAM usage: 26,87 mb)2004-12-15 12:01:49 Inserting/updating 1000 entities...2004-12-15 12:02:59 Write SQL time: 00:01:102004-12-15 12:04:06 data fetch time: 00:00:29 (RAM usage: 27,48 mb)2004-12-15 12:04:06 Total data fetch time: 00:01:06 (RAM usage: 27,48 mb)2004-12-15 12:04:06 Inserting/updating 1000 entities...2004-12-15 12:05:30 Write SQL time: 00:01:232004-12-15 12:06:05 data fetch time: 00:00:31 (RAM usage: 27,03 mb)2004-12-15 12:06:05 Total data fetch time: 00:00:35 (RAM usage: 27,03 mb)2004-12-15 12:06:05 Inserting/updating 1000 entities...2004-12-15 12:07:37 Write SQL time: 00:01:32As one can see, the Write SQL time increases per every batch.I would like this time to stay around one minute per batch.There are one trigger per table. There is one parent table which has aprimary-foreign key relationship to the three sub tables.I have 2% automatic file size growth set on both the data and the log file.Thank you in advance to the guru which helps me out with this!
View 5 Replies View RelatedHi,I have a large SQL Server 2000 database with 3 core tables.Table A : 10 million + recordsTable B : 2 million + recordsTable C : 6 million + recordsOne of the batch tasks that I have to perform firstly builds a list ofall keys for records from each of the three tables that I need toprocess (along with a flag to tell me which table the key is from).This list is populated into a table variable.I then loop through the table variable to process all the records withthe particular key value.The updates are run in order of the tables .... Table A first, B nextand finally C.The table variable will typically hold 3000 keys.My problem is this ...... the processing of the key records from TableA runs well - it takes around 40 minutes which is acceptable for thelevel of processing being carried out. Though when I start processingthe transactions for Table B the first couple of statements executesuccessfully though then the subsequent statements take a long time (insome cases hours) to complete. The format of the statements for alltables is virtually the same and the tables have been indexedappropriately.The thing is that if I alter the stored proc to only process recordsfrom Table B or Table C ... the procedure flies through and processesthe records in a flash .... 1-2 minutes.Can anyone suggest what might be the issue here ?I have read many posts though can't seem to find the solution.Should I break up my processing so that it processes each tableindividually ?I've tried running the Profiler though it doesn't provide me with muchin the way of solutions.Regards,Ian
View 6 Replies View RelatedI have an SP that is big, huge, 700-800 lines.I am not an expert but I need to figure out every possible way thatI can improve the performance speed of this SP.In the next couple of weeks I will work on preparing SQL statementsthat will create the tables, insert sample record and run the SP.I would hope people will look at my SP and give me any hints on howI can better write the SP.In the meantime, after looking at the SP briefly, my first observations are:1- use SET NOCOUNT ON2- avoid using UNION statements3- use WITH (NOLOCK) with each SELECT statement4- avoid using NESTED Select statements5- use #temp tables6- avoid renaming tables in SELECT statements, for example SELECT * FROMtblClients CAm i correct to consider the above 6 points as valid things in terms ofcausingperformance problems?I would appreciate any comments/helpThank you very much
View 12 Replies View RelatedHello,I have a question regarding stored procedure desing that provides theoptimal performance. Let's say we have a table Products that consists ofthree columns: Name, Status, RegistrationTime. All columns are indexed andusers should be able to lookup data by any of the columns. We have two mainoptions to design stored procedures for data retrieval:1. Design separate stored procedures for each search criteria:LookupProductsByName, LookupProductsByStatus, LookupProductsByTime.2. Write a generic stored procedure that will fit any search criteria:CREATE PROCEDURE GetProducts (@Name varchar(20),@Status int = NULL,@FromTime datetime = NULL,@ToTime datetime = NULL)AS BEGINSELECT[Name],[Status],[RegistrationTime]FROM [Products]WHERE [Name]=CASEWHEN @Name<>NULL THEN @NameELSE [Name]ENDAND [Status]=CASEWHEN @Status<>NULL THEN @StatusELSE [Status]ENDAND [RegistrationTime]>=CASEWHEN @FromTimestamp<>NULL THEN @FromTimestampELSE [RegistrationTime]ENDAND [RegistrationTime]<=CASEWHEN @ToTimestamp<>NULL THEN @ToTimestampELSE [RegistrationTime]ENDORDER BY [RegistrationTime]END;The second option is very attractive, because it is obviously easier tomaintain such code. However, I am a little concerned about performance ofsuch stored procedure. It is not possible to foresee what index should beused, index can only be selected each during procedure execution, becausesearch criteria can include either Name, Status or RegistrationTime. Will itmake this SP inefficient? Or perormance difference in such case is not big(if any) and we should choose the second option because of its significantcode reduction?Thanks in advanceVagif AbilovJoin Bytes!
View 3 Replies View RelatedDear all,
I have the following stored procedure that takes around 1:15 minutes to finish execution against SQL Server 2005.
The table RecordedCalls contains 9369907 Records, the other tables used in the join will not contain more than 15 Records for each one.
The table lookups contains like 200 Records so that€™s why I put the records I want from it in a temp table (#tempLookUps) before joining it with the table RecordedCalls.
I have clustered index (Primary key) on the column ID in table RecordedCalls and non-clustered indexes on the columns that are used in the Where statement and the group by field (CallType), I can€™t remove any join with other tables or any condition on the where statements hence it is very dynamic and concatenated from other strored procedure and i can't remove the DISTINCT Word.
I found that when using temp tables to put the results in then imply joining on then is more efficient than using Derived Tables.
Are there any ideas to enhance the performance for this Stored Procedure? Or the code below is the optimal code?
Here is the Stored procedure:
DECLARE @max int
SELECT @max = MAX(RecordedCalls.ID) FROM RecordedCalls
CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))
CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))
INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC
INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType
FROM Servers INNER JOIN RecordedCalls ON Servers.Name = RecordedCalls.ServerName
LEFT OUTER JOIN Tags INNER JOIN RecordedCallsTags ON Tags.ID = RecordedCallsTags.TagID
ON RecordedCalls.ID = RecordedCallsTags.CallID
WHERE RecordedCalls.ID <= @max
AND (RecordedCalls.CallDate BETWEEN CAST ('01 Jan 1910 00:00:00:000' AS DATETIME ) AND CAST('01 Jan 2210 00:00:00:000' AS DATETIME ))
AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)
AND RecordedCalls.AgentID NOT IN('1000010000')
AND RecordedCalls.IsDeleted='FALSE'
GROUP BY RecordedCalls.CallType
SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')
WHEN 'Arabic' THEN #tempLookups.NameA
ELSE #tempLookups.NameE
END AS CallsType FROM
#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code
DROP TABLE #tempLookups
DROP TABLE #tempTable
We are using a stored procedure with three parameters to query a table with two tables joined. The query when run outside of the stored procedure in Query Analyzer takes less than 1 second to run. The same query inside the stored procedure run in Query Analyzer takes 5 to 30 seconds, which is extremely long.
We have looked at parameter sniffing as the possible issue and set the parameters as local variables and tried using sp_executesql to resolve parameter sniffing. These did not work.
I copied the database to a test server with no other users logged on except for me. I ran the same stored procedure multiple times. The first run was in less than 1 second. All subsequent runs took 5 to 30 seconds. No other users were on the box; therefore, there were no file lock contentions.
We are at a loss as to why this is happening. Any advice is greatly appreciated. Our code is below:
ALTER PROCEDURE [dbo].[spBoxCheck]
@FacilityID int,
@CustID int,
@OrdNumber nvarchar(64)
AS
SET NOCOUNT ON
SELECT
dbo.PackHeader.BoxID,
dbo.OrdHeader.partnerOrderNumber,
dbo.PackLine.LineID,
dbo.PackLine.QuantityPacked,
dbo.PackHeader.Validated
FROM dbo.PackHeader
LEFT OUTER JOIN dbo.OrdHeader ON dbo.PackHeader.IntOrderNumber = dbo.OrdHeader.IntOrderNumber
LEFT OUTER JOIN dbo.PackLine ON dbo.PackHeader.BoxID = dbo.PackLine.BoxID
WHERE (OrdHeader.partnerOrderNumber = @OrdNumber)
And (OrdHeader.CustomerID = @CustID)
And (PackHeader.ProdFacilityID = @FacilityID)
Hi all,
Here€™s a question that bugs me on SQL Stored procedure.
I€™ve a stored procedure which takes 1 sec for its execution when it is hit by single user.
When the same Stored procedure is accessed concurrently in a multi user environment with different Inputs to the SP, it€™s taking 5-6 secs to execute.
I€™m totally confused, what might hold up the resources though SQL server provides multi user instances when it is hit by several users.
(Correct me if I€™m wrong).
It would be great if you let me know the actual reason behind this.
--
ash
Hi, I think I remember reading somewhere that excessive comments inside a stored procedure can have a negative impact on performance. Does anyone know if this is true and can provide an explanation?
Thank You
Hi
I need to improve the performance of Stored Procedure and would like to give some information about the same.
First of all the sp is very big. and it has some parameter like @Select, @Where etc... which may vary so it has With Recompile option.It uses patindex, substring and replace function heavily.Other things seems to be fine.
and also one more point i required assistance is
if i am declaring a variable @Month varchar(20)
and then setting value @Month = month(TradeDt) -> month function returns an int (here int to varchar conversion happening)
and also setting one more variable value like @Var1 = '0' + @Month -> reason why i declared @month as varchar
so the above option is ok or shall i go for @month as int, @Month = month(TradeDt) and @Var1 = '0' + cast(@Month as varchar)
any help or suggestion would be greatly apprecialted.
Best Regards
Yogesh
I have a stored procedure which runs in about 30-40 seconds most of the time, however sometimes it takes over an hour to complete.
The resultset is the same for both execution times.
There doesn't appear to be any other significant resource hogging on the server during execution (SQL Server does use 99% CPU while it runs tho)
The procedure itself is based on 2 views, these views in turn are themselves based on several views and some base tables and so forth.
Anyone any ideas on how to narrow the problem a little more?
Cheers.
We have an application that is based on several extended storedprocedures. When we run our application in house, or when most othercustomers run it, they see performance of about X transactions persecond. One customer is seeing performance of about X/5, and I'mhaving a hard time troubleshooting it. The performace bottleneck hasbeen narrowed to the execution of the extended stored procedures. Doesanyone know of tuneable SQL Server parameters that may specificallyaffect the performance of extended stored procedures. I know theprocedures get run by a scheduler. Is there some way the priority orfrequency of the scheduler can be modified? Thanks for any advice.
View 3 Replies View RelatedHere is the background for my question.
For my organization, I wrote a stored procedure to generate invoices for all of our clients using an audit table. Any time changes are made to the data in a table in our database, corresponding records are added to our audit table. Using this audit table we can recreate any table in the database as it was on a particular day. To generate the invoices, I first use the audit table to generate a cached view of the information. After I have a cached view, I insert records into an invoices table using various filters in my select statements.
When I run the T-SQL directly from query analizer, it takes about 1 minute to generate all of the invoices. However, when I run the same T-SQL as a stored procedure, it takes roughly 14 minutes to complete. Thinking there was a problem with too much parallelism, I restricted the MAXDOP to 2 (as the server has 2 physical processors). However, this did not reduce the execution time at all. Next, I tried using the sql profiler to watch the database while I ran the query. I checked and I did not see any unneeded recompilation. Oddly however, I noticed that the stored procedure required well over 2 million reads compared to only 400,000 reads using the straight T-SQL. I am at a loss for how to make my stored procedure run as efficiently as the straight T-SQL code. If anyone has anything else for me to try or has any suggestions, they would be greatly appreciated.
Thanks in advance,
David O'Keefe
Hi
I know that SQL Server itself does not support passing arrays to its procedures. But I need an alternative that will allow me to "duplicate" the same functionality.
I have the following information stored in a Class:
1. Userid varchar(16)
2. SessionId varchar(50)
3. LoginTime datetime
4. UserHostAddress varchar(15)
5. UserAgent varchar(150)
6. Browser varchar(255)
7. Crawler varchar(20)
8. SessionURL varchar(255)
9. SessionReferer varchar(255)
10. VisitNumber int
11. OriginalReferer varchar(255)
12. OriginalUR varchar(255)
13. Pages Array List (PageName varchar(255), ElapsedTime datetime)
I have two tables :
UserInfo: Where I keep the variables 1-12
PageInfo: Where I keep variable 13 (the list of pages)
I need to store this information in my SQL Server Database. So far I found three possible methods but I'm not sure which one has the better performance:
First Method: the easy one
1. Call the SaveUserInfo stored procedure
2. Loop through the pages array and call the SavePageInfo stored procedure for each page item in the array
Second Method: Passing a delimited string to the stored procedure
1. Call the SaveUserInfo stored procedure
2. Pass a delimeted string to the SavePageInfo stored procedure. The stored procedure will split the string and save the pages into the database. The string would look like this:
PageAddress1-ElapsedTime1|PageAddress2-ElapsedTime2|PageAddress3-ElapsedTime3...
Third Method: Passing and XML File to the stored procedure
The stored procedure will read the XML file and store the information into the database.
What method is the best for performance?? As you may see this is for tracking the user navigation through the website. For the first method I worry about the number of call to the database; for the Second and Third method I worry about the lenght of the string or XML file to pass to the stored procedure.
Any suggestions??
Thanks for any help
Sasa
When I start my stored procedure from Query Analyzer it ends in 1 h.
I have created a job consisting of 1 step - the same procedure. If I start the job from Enterprise Manager it ends after some 10 h.
What can I do to get the same performance?
Thanks in advance,
Asnate
Hi group,I have a select statement that if run against a 1 million recorddatabase directly in query analyzer takes less than 1 second.However, if I execute the select statement in a stored procedureinstead, calling the stored proc from query analyzer, then it takes12-17 seconds.Here is what I execute in Query Analyzer when bypassing the storedprocedure:USE VerizonGODECLARE @phonenumber varchar(15)SELECT @phonenumber = '6317898493'SELECT Source_Identifier,BADD_Sequence_Number,Record_Type,BAID ,Social_Security_Number ,Billing_Name,Billing_Address_1,Billing_Address_2,Billing_Address_3,Billing_Address_4,Service_Connection_Date,Disconnect_Date,Date_Final_Bill,Behavior_Score,Account_Group,Diconnect_Reason,Treatment_History,Perm_Temp,Balance_Due,Regulated_Balance_Due,Toll_Balance_Due,Deregulated_Balance_Due,Directory_Balance_Due,Other_Category_BalanceFROM BadDebtWHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =@phonenumber)order by Service_Connection_Date descRETURNGOHere is what I execute in Query Analyzer when calling the storedprocedure:DECLARE @phonenumber varchar(15)SELECT @phonenumber = '6317898493'EXEC Verizon.dbo.baddebt_phonelookup @phonenumberHere is the script that created the stored procedure itself:CREATE PROCEDURE dbo.baddebt_phonelookup @phonenumber varchar(15)ASSELECT Source_Identifier,BADD_Sequence_Number,Record_Type,BAID ,Social_Security_Number ,Billing_Name,Billing_Address_1,Billing_Address_2,Billing_Address_3,Billing_Address_4,Service_Connection_Date,Disconnect_Date,Date_Final_Bill,Behavior_Score,Account_Group,Diconnect_Reason,Treatment_History,Perm_Temp,Balance_Due,Regulated_Balance_Due,Toll_Balance_Due,Deregulated_Balance_Due,Directory_Balance_Due,Other_Category_BalanceFROM BadDebtWHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =@phonenumber)order by Service_Connection_Date descRETURNGOUsing SQL Profiler, I also have the execution trees for each of thesetwo different ways of running the same query.Here is the Execution tree when running the whole query in theanalyzer, bypassing the stored procedure:--------------------------------------Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))|--Bookmark Lookup(BOOKMARK:([Bmk1000]),OBJECT:([Verizon].[dbo].[BadDebt]))|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))|--Concatenation|--IndexSeek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Index]),SEEK:([BadDebt].[Telephone_Number]=[@phonenumber]) ORDERED FORWARD)|--IndexSeek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Redef_Index]),SEEK:([BadDebt].[Telephone_Number_Redef]=[@phonenumber]) ORDEREDFORWARD)--------------------------------------Finally, here is the execution tree when calling the stored procedure:--------------------------------------Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@phonenumber] OR[BadDebt].[Telephone_Number_Redef]=[@phonenumber]))|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),1, 10)))|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))--------------------------------------Thanks for any help on my path to optimizing this query for ourproduction environment.Regards,Warren WrightScorex Development Team
View 5 Replies View RelatedExecuting the stored procedure took 45 seconds. But copying the code to a query window and setting up the variables (instead of parameters), it took 7 seconds.
In the query window, most of the processing cost (86%) is right up front in a "Distinct Sort." But in exec stored procedure, the cost for this step is 11% and the significant costs are in later "Table Scans."
I don't know why SQL Server would choose different execution plans when the code is identical in each.
Any quick insights?
Many thanks.
Hi
I am having a problem with a particular stored procedure in a database application and I have ran out of ideas as to what is the cause. When calling this stored procedure from a .Net application it typically returns results in about 0.2 seconds. 24 hours after it's creation, the procedure takes over 40 seconds to return the same results to the application. However if I call the procedure via Management Studio or Query Analyzer, the performance remains consistently fast.
It's a fairly complicated query making use of the following features:
FOR XML EXPLICIT
The ROW_NUMBER function
Input Parameters
The procedure is replicated, along with the tables that it references
The calling application is using ExecuteXMLReader to retrieve the results.
To fix the problem, I can simply run an ALTER PROCEDURE statement (without changing any of the contents of the stored procedure). However, by the next morning, the problem will have reoccurred.
Can anyone shed any light on why this is happening?
Phil
I am trying to increase the price of an product by a user entered % for items with Dishwasher in the itemdesc.
Below is the procedure I have which doesn't appear to show any errors in the SQL Developer.
CREATE OR REPLACE PROCEDURE AdjustPrice(
pItemDesc IN ITEM.ItemDesc%TYPE,
pPercent IN NUMBER)
IS
BEGIN
UPDATE Item
SET ItemPrice = ItemPrice + ItemPrice * pPercent / 100
WHERE ItemDesc = pItemDesc;
END;
This is my run script:
BEGIN
AdjustPrice ('%Dishwasher%',10);
END;
I think the problem is with the way I have done the run script to filter to items with Dishwasher in the description. I tried LIKE an that didn't work either.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.