1. Stored Procedure runs over 90 times slower as run directly from QA
We have a table with transactions with employee_id, transactiondate and
amount (and some other fields).
I created a select query to select the sum(amount) between two dates grouped
by employee_id, and joined the sames table with all refunds (amount < 0).
The table contains about 25.000.000 records, primary key clustered index on
the transactiondate field.
When I execute the query from QA and select one month, the query returns
withing 2 seconds.
When I create a stored procedure with start/end date as parameter, the SP
runs for 3 minutes and 40 seconds.
I checked the execution plan and this is completly different. I tried an
index hint, this helped me sometimes with queries on large table, without
success. I tried all join hint, no results.
We are running SQL 2000 on a dual xeon. Here are the queries:
-- query start >>>>>>>>
Select T1.employee_id, count(t1.amount), sum(t1.amount) , t2.AmountRefunds
From Transactions T1
(Select employee_id, sum(amount) AmountRefund
Where Amount < 0 Group by employee_id ) T2
ON T1.employee_id = T2.employee_id
Group by T1.employee_id, t2.AmountRefunds
-- query end <<<<<<<<<<
When I remove the declare lines and set lines and write the create procedure
When I run
exec SP_ReturnSalesReturns '20030101' , '20030102'
This will run for more then 3 minutes.
2. seek in SQL server 2000 with VB 6.0
3. stored proc running a remote stored procedure
4. Access Application Errror
5. urgent help required - error messages when running stored proc
6. How to trigger inserting a value into another table
7. Calling a Java Stored Procedure from another Java Stored Stored Procedure
8. efficiently extracting date portion of datetime
9. Message from SQL Stored Procedure to ADO
10. Stored procedure exit without any error message
11. Turn warning message off in a store procedure
12. Gettting user input in a stored procedure to send email message
13. Getting Error Message return from a stored procedure!