Query Runs Slower When Converted To A View
Jul 23, 2005
I have a query I developed and optimised as well as possible before
converting to a view.
When running in query analyser the query runs in 15 to 18 seconds -
which is acceptable.
When "converting" into a view ( This is necessary for operational
reasons) and running with the same parameter it runs in 3 to 4
minutes.
Is there something I am unaware of ( well of course there is !!) -
I was wondering why this occurs and how I can avoid / correct the
issue.
All advice gratefully received.
Dave ( Still learning stuff about SQL Server every day!!)
View 1 Replies
ADVERTISEMENT
May 5, 2007
We're on SQL 2005 SP1, IIS6, ASP.NET 2.0Data access is 3 tier (.xsd, BLL) similar to the data tutorials.From SQL Profiler:ASP.NETexec dbo.SpGetCatalogCategories @Show='Children',@ServiceId=31016,@BrandId=NULL,@CategoryId=NULL,@ParentId=10028,@ApplicationId=NULL,@ShowUniversal=1Event Class: RPC:Completed CPU: 5,109Reads: 613,174Writes: 0Duration: 5,133Management Studioexec dbo.SpGetCatalogCategories @Show='Children',@ServiceId=31016,@BrandId=NULL,@CategoryId=NULL,@ParentId=10028,@ApplicationId=NULL,@ShowUniversal=1Event Class: SQL:BatchCompletedCPU: 407Reads: 2,182Writes: 0Duration: 409I can post the SP if it will help.This is in a production enviroment and any help is greatly appreciated.kyle
View 12 Replies
View Related
Aug 24, 2015
I'm migrating electronic records from a legacy system and the new system has strict requirements for ASCII characters in certain metadata fields. I wrote a UDF to display illegal characters, so I can work out how to map them.
The UDF used a while loop and to improve performance, I wrote the equivalent UDF using a tally table. The tally table version actually ran significantly slower. Query calling UDF using cross apply took 26 secsfor the while loop versus 119 secs for Tally table, for test data of 97000 rows
I would like to work out why, as I will use similar code to replace the illegal characters .
-- while loop version of UDF
CREATE FUNCTION [dbo].[DisplayIllegalChars](@strText VARCHAR(4000))
RETURNS @TableVariable TABLE (
Chr CHAR(1)
,AsciiValue INT)
AS
BEGIN
DECLARE @intCount INT
DECLARE @chrCheck CHAR
[Code] .....
View 9 Replies
View Related
Apr 23, 2008
Performance issue.
I have a very complex Stored Procedure called by a Job that is Scheduled to run every night.
It's execution takes sometimes 1 or 2 hours and sometimes 7 hours or more.
So, if it is running for more than 4 hours I stop the Job and I run the procedure from a Query Window and it never takes more than 2 hours.
Can anyone help me identify the problem ? I want to run from the Job and not to worry about it.
Some more information:
- It is SQL 2000 Enterprise with SP4 in a Cluster (It happens the same way in any node).
- The SQL Server and SQL Agent services run using a Domain Account that have full Administrative access.
- When I connect to a Query Window I also use a Windows Account.
- There is no locks or process bloking or being blocked while the job is running.
- Using the Task Manager the processor activity is ok, no more than 30 % in any processor.
View 15 Replies
View Related
Jul 20, 2005
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
View 15 Replies
View Related
Nov 14, 2000
Hi gurus
I haven't put the code in since I've tried several variations & keep having the same problem: I'm hoping someone will recognise the problem from a description of it.
There are two parts to my query.
* Part 1 creates and then populates a temporary table
* Part 2 is a select query which joins the temporary table to a permanent table, on 2 fields including a datetime field. The data types on each side of the join are identical.
If I run the first part of the query through ISQL and wait for it to complete before running the second part (in the same ISQL window), it (the second part) takes just 3 minutes. However, if I run both parts together, the second part takes ages, in fact I'm not sure if it completes at all (could wait indefinately!).
I tried placing a 'GO' between the two queries when running them together, but it didn't seem to help.
Please help, I'm stumped.
Thanks
Jo
View 1 Replies
View Related
Dec 3, 2006
Here a query Microsoft Access Query That I would like to convert to SQL. What replace the Word "Last" in SQL SELECT Contacts.FirstName, Last(Contacts.LastName) AS LastofLastNameFROM ContactsGROUP BY Contacts.FirstName;
View 2 Replies
View Related
Jun 12, 2002
Ok,
here's a funky one That I can't find an expanation for. If I go into EM and choose a table from a database and return all rows, I get immediate results and can start browsing records. If I go into query analyzer and do a select * on the same table, it takes up to 20-25 minutes to return the result set. This used to only take like 5 mins. What gives? Anyone seen this before?
View 1 Replies
View Related
Apr 19, 2006
I have a table valued UDF with only one SELECT query. It selects from a View, and has a couple of joins and calls another UDF in the WHERE clause. When I run the Select query itself it's duration is around 120, but when I have it wrapped in a UDF, it's around 1200!
ALTER FUNCTION [dbo].[fnGetiii]
(
bla bla bla
)
RETURNS @ItemTable TABLE
(
a varchar(2),
b bigint,
c smallmoney,
d varchar(50),
e varchar(50),
f int,
g varchar(50),
h datetime,
i int,
j int,
k int
)
AS
BEGIN
INSERT INTO @ItemTable
(a,b,c,d,e,f,g,h,i,j,k)
SELECT
vat.a AS a,
vat.b AS b,
vat.c AS c,
vat.d AS d,
vat.e AS e,
vat.f AS f,
vat.g AS g,
vat.h AS h,
vat.i AS i,
vat.j AS j,
vat.k AS k
FROM dbo.vw_jkl vat
LEFT JOIN dbo.uio uio
LEFT JOIN dbo.iop iop
ON (iop.kko = uio.kko)
ON (uio.jj = vat.jj)
WHERE this = that AND
this = that AND
this IN (SELECT iii FROM dbo.fniiireturn(pppp))
RETURN
END
How can I speed it up?
View 5 Replies
View Related
Mar 1, 2008
hi, friends, please look at this:
I have a SSIS package, and inside it I do something like below:
1. I have a SQL component, to give back a object to store the records.
2. I have a VB script component, I direct the object I got in 1 step into the script as a dataset.
My problem is:
I run the package in the SQL SERVER 2005 Store Procedue like this:
do
dtexec.exe package.dtsx
loop untill i>t
I control the it runs 30 times. But I found that the speed is slower and slower.
the first time, it takes about 600 s, but the last time, it takes the 1800 s.
Why?
The package don't drop the object it create during the loop in the Store Procedue ?
Thanks!
View 11 Replies
View Related
Jun 15, 2006
I have the following as part of a union query:
CONVERT(CHAR(8), r.RRDate, 1) AS [Date]
I also want to order by date but when I do that it doesn't order correctly because of the conversion to char data type (for example, it puts 6/15/05 before 9/22/04 because it only looks at the first number(s) and not the year). If I try to cast it back to smalldatetime in the order by clause it tells me that ORDER BY items must appear in the select list if the statement contains a UNION operator. I get the same message if I try putting just "r.RRDate" in the ORDER BY clause. It's not that big of a deal - I can lose the formatting on the date if I need to to get it to sort correctly, but this query gets used frequently and I'd like to keep the formatting if possible.
Thanks,
Dave
View 1 Replies
View Related
Nov 10, 2014
Following is my db table
student_id student_code student_parent_id student_name
1 11 0 a
2 111 1 b
3 1111 2 c
4 11111 3 d
I want to generate following op
student_id student_code student_parent_id student_name Hierarchy
1 11 0 a 11
2 111 1 b 11-111
3 1111 2 c 11-111-1111
4 11111 3 d 11-111-1111-11111
Following is the query
i want to retrieve around 10000 in one go.. its taking around 8 seconds.. how to make it faster?
even if i retrieve 1 record or 10000 records, its taking around 8 seconds...
--- create table
create table test(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))
---- insert records
insert into test values (1, '11', 0, 'a')
insert into test values (2, '111', 1, 'b')
insert into test values (3, '1111', 2, 'c')
insert into test values (4, '11111', 3, 'd')
---- result query
;WITH SInfo AS
(
SELECTsId
,scode
,ParentId
,sName
,CONVERT(nvarchar(800), scode) AS Hierarchy
[Code] .....
View 7 Replies
View Related
Dec 19, 2003
I'm running a query, actually its an insert that works when using the TSQL below.
However when I try to use the debugger to step through and using the exact same values as those below I get the following error:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Its Killing me because everything else works, but this. Can somebody help.
DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,
@BeenRead NVARCHAR(10),-- = NULL
@FK_UserIDList NVARCHAR(4000)-- = NULL
--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'
SET @FK_UserIDList = '1,2,3'
--AS
--SET NOCOUNT ON
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @Date = GETDATE()
-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)
SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,
@Job_Date,
@Start,
@Finish
-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT
COMMIT TRANSACTION
--------------------------------------------------
GO
View 1 Replies
View Related
Jul 23, 2005
I am able to run a query which runs FAst in QA but slow in theapplication.It takes about 16 m in QA but 1000 ms on theApplication.What I wanted to know is why would the query take a longtime in the application when it runs fast on SQL server?How should we try debugging it?Ajay
View 2 Replies
View Related
Aug 29, 2001
THis is so annoying. I have 3 ADO executes in my program. THe first one creates a view, the second one performs an outer join on that view and returns a result set, the third execute drops the aforementioned view. THe program that is using this is installed on about 200 computers scattered across Germany and Italy, all querying the same MSsql server 7.0. THe queries run quite quick when few users are actively using the program (after hours for example). however in the heat of the day performance goes up and down dramatically with identical queries taking from 1 to 20 seconds to return their result set. Now I initially thought 'bandwidth issue out of our server'. However I noticed that if I take those three queries and run them from the sql server enterprise manager( running on the same computer as the aforementioned program) then the queries run instantly and the data is in my result pane in less than 2 seconds ALWAYS....even when the program is dogging it with 20 second delays before the result set returns. I know it is hanging on the return of the result set as I put a stop after before each ADO execute in order to check which one was eating up my time. Why is there this dichotomy between running the queries from my enterprise manager versus running them from an ADO object. Both are using TCP/IP (no named pipes involved). I havent monkied with the attributes of the ADO result set so they are all set to default.
I have used the sql server profiler to trace these queries and they always run in less than 33 milliseconds. THe duration is also never more than 33 milliseconds. THis stinks of a network resource issue but what always leads me somewhere else is how consistent the performance of the enterprise manager is when it runs the exact same three queries.
Here is my slightly edited connection string
Public Const connection_string = "Provider=SQLOLEDB;Server=000.000.000.000;" & _
"User ID=johndoe;Password=janedoe;Network=dbmssocn;" & _
"database=fidojoe"
Here are the 3 ADO executes:
conn.Execute (sqlstr_create_view)
Set resultset1 = conn.Execute(sqlstr_get_providers_by_DMISID)
conn.Execute (sqlstr_drop_view)
View 3 Replies
View Related
Feb 19, 2008
Hi Guys,
I've got a stored proc used for order generation which runs long sometimes when called from within our app. A normal run will complete within 20s, a long run will get terminated by the app at the 6 minute mark.
When it runs long once, repeated attempts will also do so until I execute the same query the app did, but from within Query Analyzer. At which time the problem will disappear for a day or two. The app connects to the SQL Server 200 SP4 database using ADO.
I suspected statistics might be at fault here but have tried both "UPDATE STATISTICS table WITH FULLSCAN" and "DBCC DBREINDEX('table') to no avail. This issue has occurred and been worked around in this manner a few dozen times.
Any idea what might be going on here?
View 7 Replies
View Related
Mar 11, 2008
Good afternoon,
i'm new to Functions on the SQL server
I'm trying to create a dynamic query that would select the the column passed to the function from a certain table
my table called selected_Date, and has StartDate, and EndDate columns
when the user select for example "StartDate", i pass this as a variable to the function which runs the query. but i always gets back the passed string as a result..
here is my table
StartDate | EndDate
---------------------
20071231 | 20080306
here is my function knowing that i'm passing "Start" as a variable:
Code:
ALTER FUNCTION tu_efgn_int.Get_StartEndDates(@DateType varchar(10))
RETURNS varchar(8)
AS
BEGIN
DECLARE @vDate varchar(15)
Set @vDate= (Select @DateType + 'Date' From dbo.ps_tbl_SelectedDates)
RETURN @vDate
END
@vDate always returns "StartDate" as result instead of 20071231 why is that ?
View 2 Replies
View Related
Jan 15, 2008
Hi,
I am running the following MDX query through a DataReader and a Ado.Net Connection.
SELECT
{[Measures].[Deuda Total Nacional],
[Measures].[Deuda Total Nacional Maximo],
[Measures].[Cupo Nacional],
[Measures].[Porcentaje Utilizacion Maximo],
[Measures].[Pago Minimo Estado Cuenta],
[Measures].[Deuda Ultima Facturacion],
[Measures].[Dias Mora],
[Measures].[Dias Mora Maximo],
}
ON COLUMNS
,[Cuenta].[Cuenta].[Cuenta]*[Cuenta].[Rut].[Rut]*[Cuenta].[Dv].[Dv] ON ROWS
FROM [Bd Rtd]
WHERE [Tiempo].[Mes].&[2007-09-01T00:00:00]
The thing is, when I have about 10 thousand rows It runs in about 50 seconds which is good, but when I run this query and I have processed the cube with 100 thousand rows it runs out of memory and crashes.
I'm working in a shared development server with 1GB of memory for my project.
Is there any way to make it run anyway?? I mean even if it has to swap.
thanks
By the way when this thing goes into production it will have 1.5 million rows
View 2 Replies
View Related
Aug 23, 2000
I have a query which joins multiple tables. This query has suddenly begun to take up to 2 minutes to run (vs. 5-10 seconds previously).
No major change in number of records in the tables (currently about 220,000). When I remove the PK from one of the tables which then forces a tablescan, the query returns to running in 5-10 seconds. If I add the PK back, performance is back to 2 minutes.
Any suggestions short and/or long term?
Thanks for any help.
View 1 Replies
View Related
Jun 30, 2014
I have two servers: one production and one development. There is a third party query that runs on both servers. Yes, the query is poorly tuned - I cannot change it. In production, it runs in 54 minutes. In development, I have tried to let it run for days and it never completes.
Here's what I have tried so far:
Compared the settings of production vs. development. The settings are very similar - the development box is larger, 4 times more memory.
Max degree of parallelism is the same on both boxes.
No compression on both boxes.
The production server is fairly busy, the development server is empty - this is the only process running on it.
Plenty of free disk space.
Updated all statistics on all databases touched by the query on dev.
Indexing is the same on both boxes.
The development box is running MSSQL2012.
The production box is running MSSQL2008R2.
What I've noticed:
The query consumes a massive amount of CPU time.
HUGE number of reads (16 million reads for 10 writes according to sp_WhoIsActive)
Largest wait types are CXPACKET, SOS_SCHEDULER_YIELD and TRACEWRITE respectively.
wait_typesum_wait_time_mspct_wait_timesum_waiting_tasksavg_wait_time_ms
CXPACKET4142765580.7176307423.5
SOS_SCHEDULER_YIELD24146944.7532725530.0
TRACEWRITE19856343.914831338.9
View 9 Replies
View Related
Aug 29, 2007
I would like to create a query to find what user owns the job. It probably is in the master db, but I wouldn't know where to begin other than that. Telling me how to either change the job owner or create a job through t-sql would also help. Thanks
-Kyle
View 1 Replies
View Related
Jul 18, 2007
Hi All,
Recently we started using Pass throughs to perofmr large inserts, however we have started to notice that some of these pass throughs are executing twice, and therefore duplicating data.
Is this a known bug, and if not has anyone got any advice on what could be causing it?
We're connecting from Access 2002 (SP6) to SQL Server 2000 Enterprise.
The conenction string / code is as follows :-
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.Properties("Jet OLEDBDBC Pass-Through Statement") = True
cmd.Properties _
("Jet OLEDB*** Through Query Connect String") = _
"ODBC;DSN=" & myDatabaseShort & ";DATABASE=" & myDatabase & ";UID=sa;PWD=" & Left(myDatabaseShort, 4) & ";"
It connects fine. My sql string is a straightforward Insert statement that only executes once via SQL Query Analyzer.
I'm calling the pass through using the following lines of code:-
cmd.CommandText = mysql
cmd.Execute
Can anyone see anything obvious that I'm doing wrong, or is this a known issue?
Cheers
View 5 Replies
View Related
Jan 8, 2008
Ok, I'll admit right off the bat that I never suspected that I'd ever raise this complaint, much less worry about how to fix the "problem" associated with it!
We're preparing to take a large set of changes (projects) to PeopleSoft Financials from development to test. The code is still somewhat rough, but it has been "desk checked" to ensure that it does what the developers think that it ought to do, and they've blessed it at that point. The code is now moving into the test phase, and the QA team is finding locking/blocking issues that we've never seen in this code before... Sort of a "lock avalanche" where no one process locks for very long, but many of them block one another to the point where applications actually "freeze" while almost never hitting a deadlock.
My solution was to create a "blitzkrieg" query / stored procedure that would periodically sample master.dbo.sysprocesses, master.dbo.sysdatabases, and apply one of the dm_ functions to gather information on locking, blocking, and deadlocking. My procedure runs nicely (it never hangs) and gets about 99.3% of the data that I want.
The problem is that the blasted query / stored procedure runs either too fast or too slow, depending on how you look at it. Because the dm_ function takes a few ms to run, there can be a situation where either a row appears as a false positive or as a missing row because of timing... Either the culprit shows up as a blocker, but by the time the victim spid is evaluated the block has cleared, or the row is skipped and by the time the victim is evaluated the block has occured.
The whole process runs in well under 100 ms when there is nothing to report, and I've never seen it run 200 ms yet under the worst conditions it has faced, so the code is fast... The problem is that I really don't want to try to enforce any kind of locking to resolve the issue, because that locking would impact performance and that is EXACTLY what I do NOT want to do.
Any suggestions?
-PatP
View 8 Replies
View Related
Jul 20, 2005
A colleague of mine has a query which fails to run under SQLAgentbatch with the following error:The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value. [SQLSTATE 22007] (Error 242) Thestatement has been terminated. [SQLSTATE 01000] (Error 3621). Thestep failed.He can run the same query sucessfully via query analyzer (i.e. noerrors, and it does what he wants)If I try to run the same query through Query Analyzer on myworkstation, I get a different error altogether:Server: Msg 242, Level 16, State 3, Procedure DateForGrouping, Line 11The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.Any idea what might be causing these differences in behaviourdepending on how and/or where the query is run from?The (working) statement in question is:================================================== ======================insert into Summary_ReferrerSalesselect DateCreated,ReferrerID,ReferrerIDCount,PUIDCount,ReferrerDescription,0 as TotalOrderValue,0 as TotalOrderLinesfrom vw_ReferrerPopularityWarning: Null value is eliminated by an aggregate or other SEToperation.(11996 row(s) affected)================================================== ====================And the table / view / function definitions (I take no responsibilityfor the view definition!) are:CREATE TABLE [Summary_ReferrerSales] ([DateCreated] [datetime] NULL ,[ReferrerID] [char] (2) NULL ,[ReferrerIDCount] [int] NULL ,[PUIDCount] [int] NULL ,[ReferrerDescription] [nchar] (100) NULL ,[TotalOrderValue] [numeric](18, 0) NULL ,[TotalOrderLines] [numeric](18, 0) NULL) ON [PRIMARY]CREATE VIEW dbo.vw_ReferrerPopularityASSELECT TOP 100 PERCENT COUNT(dbo.LogReferrerID.Referrer) ASReferrerIDCount,COUNT(DISTINCT dbo.LogReferrerID.PUID) AS PUIDCount,dbo.DateForGrouping(dbo.LogReferrerID.DateUsed)AS DateCreated,dbo.LogReferrerID.Referrer AS ReferrerID,dbo.LookupReferrerID.ReferrerDescriptionFROMdbo.LogReferrerID INNER JOIN dbo.LookupReferrerIDON dbo.LogReferrerID.Referrer = dbo.LookupReferrerID.ReferrerIDWHERE (dbo.LogReferrerID.DateUsed > CONVERT(DATETIME, '2003-09-0100:00:00', 102))GROUP BY dbo.LogReferrerID.Referrer,dbo.DateForGrouping(dbo.LogReferrerID.DateUsed),dbo.LookupReferrerID.ReferrerDescriptionHAVING (dbo.LogReferrerID.Referrer <> 'WS')AND (COUNT(dbo.LogReferrerID.Referrer) IS NOT NULL)AND (dbo.LookupReferrerID.ReferrerDescription IS NOT NULL)AND (dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) >CONVERT(DATETIME, '2003-09-01 00:00:00', 102))AND (COUNT(DISTINCT dbo.LogReferrerID.PUID) IS NOT NULL)ORDER BY dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) DESCCREATE TABLE [LogReferrerID] ([LogReferrerID] [int] NULL ,[Referrer] [varchar] (2) NULL ,[DateUsed] [smalldatetime] NULL ,[HTTPReferrer] [varchar] (1000) NULL ,[TargetURL] [varchar] (1000) NULL ,[QueryString] [varchar] (1000) NULL ,[PUID] [varchar] (50) NULL) ON [PRIMARY]CREATE TABLE [LookupReferrerID] ([ReferrerID] [char] (2) NOT NULL ,[ReferrerDescription] [varchar] (100) NOT NULL ,CONSTRAINT [PK_Lookup_ReferrerID] PRIMARY KEY CLUSTERED([ReferrerID]) ON [PRIMARY]) ON [PRIMARY]CREATE FUNCTION dbo.DateForGrouping (@RowDate datetime)RETURNS datetime ASBEGINdeclare @datestring char(10)set @datestring=cast(datepart(dd,@RowDate) as char(2)) + '/' +cast(datepart(mm,@RowDate) as char(2)) + '/' +cast(datepart(yyyy,@RowDate) as char(4))return cast(@datestring as datetime)END
View 3 Replies
View Related
Dec 14, 2007
Hi all,
I know this sounds rather dumb but my select statement is running forever!
I am trying to execute the statement through my C# code.
If I try to run the query through sql server management studio, sometimes it runs fine, but sometimes it keeps running and never returns a value.
I am doing something like this:
###########################################################################################
String query = "SELECT studentID from StudentTable WHERE studentDeptID = '100' AND deptName = 'CS'";
SqlCommand command = new SqlCommand(query, connection);
Object myObject = command.ExecuteScalar();
############################################################################################
StudentTable contains roughly 1000 rows.
Somebody please help me out of this. Thanks in advance.
Surya
View 11 Replies
View Related
Mar 8, 2007
This is in ASP.NET 1.1. I have a performance problem with a webform. The form contains several bound fields and a couple datagrids. I fill the grids by creating a data adapter, then I use the adapter to fill a dataset, then I set the grid datasource to the dataset.
The query to fill one of the grids is getting a SQL timeout when running from my application (it takes about 40 seconds to complete). When I run the same SQL code from SQL Query Analyzer it runs in less than 1 second. (it is embedded sql in the app, not a stored procedure).
I suspect that something else is being requested from SQL during the postback that is causing a blocking issue or something but I can't tell exactly what is happening. I've tried tracing through the code and all that I can tell is that the timeout occurs during the dataadapter.fill command.
Has anyone else seen something similar? Is there a good way for me to see what SQL commands are being executed from ASP.NET? Any advice on debugging this would be much appreciated.
View 1 Replies
View Related
Aug 15, 2001
The following query runs fine on sql 7.0 but it kind of hangs/keeps running without any output on sql server 2000
---------------------
Set @cmd = 'Update ABCD Set
'+@day+'_LB = IsNull(LB,0),
'+@day+'_UT = IsNull(UNIT,0)
From tempData as T
Where T.STORE_NUM = ABCD.STORE_NUM And T.ITM_ID = ABCD.UPC'
execute (@cmd)
------------------------------------
But if we hard code the @day parameter and run the query like this it runs very fast on sql 2000....
Update ABCD Set THIRD_LB = IsNull(LB,0),
THIRD_UT = IsNull(UNIT,0)
From tempData as T
Where T.STORE_NUM = ABCD.STORE_NUM And T.ITM_ID = ABCD.UPC
------------------------------
View 2 Replies
View Related
Dec 29, 2003
I have an stored procedure that takes 23 seconds to execute. However, the same query is executed in 4 seconds.
All the tests where made using the Query Analizer.
What could be wrong?
Thanks a lot in advance.
View 13 Replies
View Related
Jul 21, 2015
I am able to select the average, max, etc of the variable in a simple select statement but when I do the following, it doesn't work. The reason I am doing the following is because I am calculating the average and such over a 5 min interval, then saving the output as one line. That is why I condense average, max, min, and stdev into one variable which I then output. why it won't run when I have it like this but will run when it is like this?
will run:
SELECT AVG(NacTemp), MAX(NacTemp),MIN(NacTemp), STDEV(NacTemp)
FROM [DATABASE]
WHERE [UTCDeviceTimeStamp] between DATEADD(minute, -5, GETUTCDATE()) and GETUTCDATE()
won't run but I need it to:
DECLARE @now datetime SET @now = GETUTCDATE() --Universal Time
DECLARE @timeint int SET @timeint = '5' --time interval in minutes
DECLARE @time datetimeSET @time = (SELECT MIN([UTCDeviceTimeStamp]) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] BETWEEN DATEADD(minute, -@timeint,@now) AND @now) -- Timestamp data will be saved as
DECLARE @comma varchar(4) SET @comma = ', '
[Code] ....
View 9 Replies
View Related
Mar 28, 2008
I have created a lot of reports using this technique, but this is the first one that doesn't work. As there is absolutely nothing special about it, I can't figure out what the issue is.
I have one dataset that uses parameters chosen from two other dataset results. One dataset result runs just fine and returns values in reporting services, but the other returns blank in reporting services. In Visual Studio, both datasets return data.
I cannot for the life of me figure out what I've done that gives this result as I've never encountered it before and use this method of parametization quite frequently.
The dataset in question doesn't even join any tables, it's a direct select distinct field"1" from table"a".
I'm running SQL2005, SP2.
Thanks for any advice.
Margaret
View 6 Replies
View Related
Oct 9, 2001
I have a stored proceedure (which I will tag on at the end for those interested) which is taking at least 15 minutes to run when executed, but completes in 1 minute when the tsql statement is run in Query Analyser. Why is this?
I suspect that it may be connected to table indexing, but why then is this bypassed when QA is used?
Any advice appreciated.
Derek
************************************************** ***********************
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_ValidateAIGL') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.sp_ValidateAIGL
GO
CREATE PROCEDURE dbo.sp_ValidateAIGL
@IGLPeriodIDInt,
@IGLProgramIDInt
AS
/* ************************************************** ************************
Name:sp_ValidateIGL
Author:CL
Date:19-Jan-2001
Notes:
************************************************** ************************ */
--SET NOCOUNT ON
DECLARE@TaxYearChar(5),
@FrequencyChar(1),
@PeriodNo Int,
@ProgramLogIDInt
SELECT@TaxYear = TaxYear,
@PeriodNo = PeriodNo,
@Frequency = Frequency
FROMtbl_IGLPeriods
WHEREIGLPeriodID = @IGLPeriodID
SELECT @ProgramLogID = (SELECT ProgramLogID FROM tbl_SYSProgramLogs WHERE IGLProgramID = @IGLProgramID AND IGLPeriodID = @IGLPeriodID)
CREATE TABLE #IGLErrors
(
KeyFieldChar(24),
ErrorIDInt,
DescriptionVarChar(255)
)
-- *** Global Non Program Specific Data Errors ***
-- CHECK - that there are records in the DEB_IGL_PAYROLL_OUTPUT file.....none and the routine failed...
IF NOT EXISTS(SELECT * FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID)
INSERT INTO #IGLErrors SELECT NULL, 100, 'No records were processed by the IGL run!'
SELECT * FROM #IGLErrors
-- CHECK - search for any records where the employee's EXPENSE_CODE is NULL
INSERT INTO #IGLErrors
SELECT DISTINCT
NULLIF(EmpNo, ''),
2,
'Employee "' + COALESCE(NULLIF(RTRIM(EmpNo), ''),
'<Missing Employee>') + '" (Organisation Unit - ' + COALESCE(RTRIM(OrgUnitCode),
'<No Organisation Unit>') + ') does not have a EXPENSE_CODE Code.'
FROM tbl_OUT_Payroll
WHERE NULLIF(ExpenseCode, '') IS NULL
ANDIGLProgramID = @IGLProgramID
SELECT * FROM #IGLErrors
-- CHECK - check that the BALANCE of DEBITs match the balance of CREDITs
IF (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 1 AND IGLProgramID = @IGLProgramID) <> (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 0 AND IGLProgramID = @IGLProgramID)
INSERT INTO #IGLErrors SELECT NULL, 3, 'The total cash value for DEBIT elements does not match the total cash for CREDIT elements.'
SELECT * FROM #IGLErrors
-- *** Program 1 and 2 errors ***
IF @IGLProgramID IN (1, 2)
BEGIN
-- CHECK - search for any records where the employee's COST_CENTRE is NULL
INSERT INTO #IGLErrors
SELECT DISTINCT
NULLIF(EmpNo, ''),
1,
'Employee "' + NULLIF(RTRIM(EmpNo), '') + '" (Organisation Unit = ' + RTRIM(OrgUnitCode) + ') does not have a COST_CENTRE Code.'
FROM tbl_OUT_Payroll
WHERE NULLIF(CostCenter, '') IS NULL
ANDIGLProgramID = @IGLProgramID
SELECT * FROM #IGLErrors
-- Check for EMPLOYEEs that were not transfered to the PAYROLL output (usually caused by missing ORG_UNITs or not picked up in
-- the DEB_VIEW_APPOINTEE view...)
INSERT INTO #IGLErrors
SELECT DISTINCT
EMP_NO,
11,
'Employee "' + RTRIM(EMP_NO) + '" was excluded from the summary. Check their Organisation Unit codes!'
FROM PSELive.dbo.COSTING_OUTPUT
WHERENOT EMP_NO IN (SELECT DISTINCT EmpNo FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID)
ANDPERIOD_NO = @PeriodNo
ANDTAX_YEAR = @TaxYear
SELECT * FROM #IGLErrors
-- Check that there are no ELEMENTS in the COSTING_OUTPUT table that don't exist in the tbl_IGLElements table
INSERT INTO #IGLErrors
SELECT DISTINCT
ELEMENT,
12,
'Element "' + RTRIM(ELEMENT) + '" does not exist in the IGL Interface Elements table!'
FROM PSELive.dbo.COSTING_OUTPUT
WHERE ELEMENT NOT IN
(
SELECT DISTINCT Element
FROM tbl_IGLElements
)
ANDPERIOD_NO = @PeriodNo
SELECT * FROM #IGLErrors
END
-- *** Add a error to indicate the number of errors ***
IF EXISTS (SELECT * FROM #IGLErrors)
INSERT INTO #IGLErrors
SELECT 0,
0,
'Warning, there are ' + CAST(Count(*) AS VarChar(5)) + ' recorded errors!'
FROM#IGLErrors
-- Transfer the records to the ErrorsLog table ready for the user to view...
DELETE FROM tbl_SYSErrorsLog
INSERT INTO tbl_SYSErrorsLog (IGLProgramID, OutputLogID, KeyField, ErrorID, Description)
SELECT@ProgramLogID,
@IGLPeriodID,
KeyField,
ErrorID,
Description
FROM #IGLErrors
ORDER BY ErrorID
DROP TABLE #IGLErrors
SELECT *
FROM tbl_SYSErrorsLog
ORDER BY ErrorID
--SET NOCOUNT OFF
GO
GRANT EXECUTE ON dbo.sp_ValidateAIGL TO Public
GO
View 2 Replies
View Related
Jun 25, 2007
The query below runs in sub second time if I don't call it as a stored procedure. I have looked at the execution plan for both the query and the query as a stored procedure and they are the same.
When I put the query into a stored procedure it takes over 2 minutes to run.
All feedback (even the ugly stuff) is more than welcome. I want to master this issue and forever put it behind me.
This is the sql when I just execute it outright:1 DECLARE
2 @WebUserID nvarchar(20)
3 ,@DocumentTypeID int
4 ,@RouteID nvarchar(10)
5 ,@CustomerID nvarchar(15)
6 ,@DocumentIDPrefix nvarchar(20)
7 ,@StartDate datetime
8 ,@EndDate datetime
9 ,@OversoldOnly bit
10 ,@DexCustomersOnly bit
11 ,@DeviationsOnly bit
12 ,@CashNoPaymentOnly bit
13 ,@SignatureName nvarchar(45)
14 ,@SortExpression varchar(200)
15 ,@StartRowIndex int
16 ,@MaximumRows int
17
18 SET @WebUserID = 'manager'
19 SET @DocumentTypeID = 0
20 SET @DocumentIDPrefix = '%'
21 SET @StartDate = '04/17/2007'
22 SET @EndDate = '04/19/2007'
23 SET @OversoldOnly = 0
24 SET @DexCustomersOnly = 0
25 SET @DeviationsOnly = 0
26 SET @CashNoPaymentOnly = 0
27 SET @SortExpression = ''
28 SET @StartRowIndex = 0
29 SET @MaximumRows = 20;
30
31 WITH OrderedDocumentHistory AS
32 (
33 SELECT
34 dh.DocumentHistoryID
35 ,dh.DocumentID
36 ,dh.DocumentTypeID
37 ,dh.DocumentTypeDesc
38 ,dh.RouteID
39 ,dh.RouteDesc
40 ,dh.CustomerID
41 ,dh.CustomerName
42 ,dh.DocDate
43 ,ISNULL(dc.HasReceipt, 0) AS 'HasReceipt'
44 ,ddt.Description AS 'SignatureReason'
45 ,a.Amount
46 ,ROW_NUMBER() OVER (ORDER BY dh.DocDate DESC) AS 'RowNumber'
47 FROM
48 DocumentHistory dh
49 INNER JOIN Customers c ON dh.CustomerID = c.CustomerID
50 INNER JOIN DeviationTypes ddt ON dh.DriverDeviationTypeID = ddt.DeviationTypeID
51 INNER JOIN
52 (
53 SELECT
54 DocumentHistoryID
55 ,(COALESCE(SUM((CONVERT(INT, Units + DeviationUnits)) * (UnitPrice - UnitDiscount)) + SUM((CONVERT(INT, Cases + DeviationCases)) * (CasePrice - CaseDiscount)), 0.0)) AS Amount
56 FROM
57 DocumentHistoryItems dhia
58 GROUP BY
59 dhia.DocumentHistoryID
60 ) AS a ON a.DocumentHistoryID = dh.DocumentHistoryID
61 LEFT OUTER JOIN
62 (
63 SELECT DISTINCT
64 dca.DocumentID
65 ,1 AS 'HasReceipt'
66 FROM
67 DocumentCollections dca
68 ) AS dc ON dh.DocumentID = dc.DocumentID
69 WHERE
70 dh.DocDate BETWEEN @StartDate AND @EndDate
71 AND (dh.DocumentTypeID = @DocumentTypeID OR @DocumentTypeID IS NULL)
72 AND (dh.RouteID = @RouteID OR @RouteID IS NULL)
73 AND (dh.CustomerID = @CustomerID OR @CustomerID IS NULL)
74 AND dh.DocumentID LIKE @DocumentIDPrefix
75 AND CASE WHEN @OversoldOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits > 0 OR dhio.DeviationCases > 0)), 0) ELSE 1 END > 0
76 AND CASE WHEN @DexCustomersOnly = 1 THEN c.DEXEnable ELSE 'Y' END = 'Y'
77 AND CASE WHEN @DeviationsOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits != 0 OR dhio.DeviationCases != 0)), 0) ELSE 1 END != 0
78 AND CASE WHEN @CashNoPaymentOnly = 1 THEN dh.Terms ELSE 'CHECK/CASH' END = 'CHECK/CASH'
79 AND CASE WHEN @CashNoPaymentOnly = 1 THEN (SELECT MAX(dhio.AlcoholPct) FROM DocumentHistoryItems dhio WHERE dhio.DocumentHistoryID = dh.DocumentHistoryID) ELSE 1 END > 0
80 AND CASE WHEN @CashNoPaymentOnly = 1 THEN ISNULL(dc.HasReceipt, 0) ELSE 0 END = 0
81 AND (dh.SigName = @SignatureName OR @SignatureName IS NULL)
82 AND (c.WarehouseID IN (SELECT WarehouseID FROM WebUserWarehouses WHERE WebUserID = @WebUserID)
83 OR @WebUserID IS NULL)
84 )
85
86 SELECT
87 DocumentHistoryID
88 ,DocumentID
89 ,DocumentTypeDesc
90 ,RouteID
91 ,RouteDesc
92 ,CustomerID
93 ,CustomerName
94 ,DocDate
95 ,Amount
96 ,HasReceipt
97 ,SignatureReason
98 FROM
99 OrderedDocumentHistory
100 WHERE
101 RowNumber BETWEEN (@StartRowIndex + 1) AND (@StartRowIndex + @MaximumRows) Here is the sql for creating the stored procedure. 1 CREATE Procedure w_DocumentHistory_Select
2 (
3 @WebUserID nvarchar(20)
4 ,@DocumentTypeID int
5 ,@RouteID nvarchar(10)
6 ,@CustomerID nvarchar(15)
7 ,@DocumentIDPrefix nvarchar(20)
8 ,@StartDate datetime
9 ,@EndDate datetime
10 ,@OversoldOnly bit
11 ,@DexCustomersOnly bit
12 ,@DeviationsOnly bit
13 ,@CashNoPaymentOnly bit
14 ,@SignatureName nvarchar(45)
15 ,@SortExpression varchar(200)
16 ,@StartRowIndex int
17 ,@MaximumRows int
18 )
19 AS
20 SET NOCOUNT ON
21
22 IF LEN(@SortExpression) = 0 OR @SortExpression IS NULL
23 SET @SortExpression = 'Number DESC'
24
25 IF @StartRowIndex IS NULL
26 SET @StartRowIndex = 0
27
28 IF @MaximumRows IS NULL
29 SELECT
30 @MaximumRows = COUNT(dh.DocumentHistoryID)
31 FROM
32 DocumentHistory dh;
33
34 WITH OrderedDocumentHistory AS
35 (
36 SELECT
37 dh.DocumentHistoryID
38 ,dh.DocumentID
39 ,dh.DocumentTypeID
40 ,dh.DocumentTypeDesc
41 ,dh.RouteID
42 ,dh.RouteDesc
43 ,dh.CustomerID
44 ,dh.CustomerName
45 ,dh.DocDate
46 ,ISNULL(dc.HasReceipt, 0) AS 'HasReceipt'
47 ,ddt.Description AS 'SignatureReason'
48 ,a.Amount
49 ,CASE
50 WHEN @SortExpression = 'Number DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocumentID DESC))
51 WHEN @SortExpression = 'Number ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocumentID ASC))
52 WHEN @SortExpression = 'CustomerName DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.CustomerName DESC))
53 WHEN @SortExpression = 'CustomerName ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.CustomerName ASC))
54 WHEN @SortExpression = 'CompletedDate DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocDate DESC))
55 WHEN @SortExpression = 'CompletedDate ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.DocDate ASC))
56 WHEN @SortExpression = 'RouteDescription DESC' THEN (ROW_NUMBER() OVER (ORDER BY dh.RouteDesc DESC))
57 WHEN @SortExpression = 'RouteDescription ASC' THEN (ROW_NUMBER() OVER (ORDER BY dh.RouteDesc ASC))
58 END AS 'RowNumber'
59 FROM
60 DocumentHistory dh
61 INNER JOIN Customers c ON dh.CustomerID = c.CustomerID
62 INNER JOIN DeviationTypes ddt ON dh.DriverDeviationTypeID = ddt.DeviationTypeID
63 INNER JOIN
64 (
65 SELECT
66 DocumentHistoryID
67 ,(COALESCE(SUM((CONVERT(INT, Units + DeviationUnits)) * (UnitPrice - UnitDiscount)) + SUM((CONVERT(INT, Cases + DeviationCases)) * (CasePrice - CaseDiscount)), 0.0)) AS Amount
68 FROM
69 DocumentHistoryItems dhia
70 GROUP BY
71 dhia.DocumentHistoryID
72 ) AS a ON a.DocumentHistoryID = dh.DocumentHistoryID
73 LEFT OUTER JOIN
74 (
75 SELECT DISTINCT
76 dca.DocumentID
77 ,1 AS 'HasReceipt'
78 FROM
79 DocumentCollections dca
80 ) AS dc ON dh.DocumentID = dc.DocumentID
81 WHERE
82 dh.DocDate BETWEEN @StartDate AND @EndDate
83 AND (dh.DocumentTypeID = @DocumentTypeID OR @DocumentTypeID IS NULL)
84 AND (dh.RouteID = @RouteID OR @RouteID IS NULL)
85 AND (dh.CustomerID = @CustomerID OR @CustomerID IS NULL)
86 AND dh.DocumentID LIKE @DocumentIDPrefix
87 AND CASE WHEN @OversoldOnly = 1 THEN ISNULL( (SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits > 0 OR dhio.DeviationCases > 0)), 0) ELSE 1 END > 0
88 AND CASE WHEN @DexCustomersOnly = 1 THEN c.DEXEnable ELSE 'Y' END = 'Y'
89 AND CASE WHEN @DeviationsOnly = 1 THEN ISNULL((SELECT TOP 1 (dhio.DeviationUnits + dhio.DeviationCases) FROM DocumentHistoryItems dhio WHERE dh.DocumentHistoryID = dhio.DocumentHistoryID AND (dhio.DeviationUnits != 0 OR dhio.DeviationCases != 0)), 0) ELSE 1 END != 0
90 AND CASE WHEN @CashNoPaymentOnly = 1 THEN dh.Terms ELSE 'CHECK/CASH' END = 'CHECK/CASH'
91 AND CASE WHEN @CashNoPaymentOnly = 1 THEN (SELECT MAX(dhio.AlcoholPct) FROM DocumentHistoryItems dhio WHERE dhio.DocumentHistoryID = dh.DocumentHistoryID) ELSE 1 END > 0
92 AND CASE WHEN @CashNoPaymentOnly = 1 THEN ISNULL(dc.HasReceipt, 0) ELSE 0 END = 0
93 AND (dh.SigName = @SignatureName OR @SignatureName IS NULL)
94 AND (c.WarehouseID IN (SELECT WarehouseID FROM WebUserWarehouses WHERE WebUserID = @WebUserID)
95 OR @WebUserID IS NULL)
96 )
97 SELECT
98 DocumentHistoryID
99 ,DocumentID
100 ,DocumentTypeDesc
101 ,RouteID
102 ,RouteDesc
103 ,CustomerID
104 ,CustomerName
105 ,DocDate
106 ,Amount
107 ,HasReceipt
108 ,SignatureReason
109 FROM
110 OrderedDocumentHistory
111 WHERE
112 RowNumber BETWEEN (@StartRowIndex + 1) AND (@StartRowIndex + @MaximumRows)
Here is the code for calling the stored procedure:1 DECLARE @RC int
2 DECLARE @WebUserID nvarchar(20)
3 DECLARE @DocumentTypeID int
4 DECLARE @RouteID nvarchar(10)
5 DECLARE @CustomerID nvarchar(15)
6 DECLARE @DocumentIDPrefix nvarchar(20)
7 DECLARE @StartDate datetime
8 DECLARE @EndDate datetime
9 DECLARE @OversoldOnly bit
10 DECLARE @DexCustomersOnly bit
11 DECLARE @DeviationsOnly bit
12 DECLARE @CashNoPaymentOnly bit
13 DECLARE @SignatureName nvarchar(45)
14 DECLARE @SortExpression varchar(200)
15 DECLARE @StartRowIndex int
16 DECLARE @MaximumRows int
17
18 SET @WebUserID = 'manager'
19 SET @DocumentTypeID = 0
20 SET @DocumentIDPrefix = '%'
21 SET @StartDate = '04/17/2007'
22 SET @EndDate = '04/19/2007'
23 SET @OversoldOnly = 0
24 SET @DexCustomersOnly = 0
25 SET @DeviationsOnly = 0
26 SET @CashNoPaymentOnly = 0
27 SET @SortExpression = ''
28 SET @StartRowIndex = 0
29 SET @MaximumRows = 20;
30
31 EXECUTE @RC = [Odom].[dbo].[w_DocumentHistory_Select]
32 @WebUserID
33 ,@DocumentTypeID
34 ,@RouteID
35 ,@CustomerID
36 ,@DocumentIDPrefix
37 ,@StartDate
38 ,@EndDate
39 ,@OversoldOnly
40 ,@DexCustomersOnly
41 ,@DeviationsOnly
42 ,@CashNoPaymentOnly
43 ,@SignatureName
44 ,@SortExpression
45 ,@StartRowIndex
46 ,@MaximumRows
View 3 Replies
View Related
