Tagged Questions
1
vote
1answer
98 views
Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure
While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost.
ie:
CREATE OR REPLACE FUNCTION my_function()
RETURNS setof record AS
$BODY$
-- ...
1
vote
1answer
749 views
MySQL: how to optimize the stored procedure?
MySQL 5.5.28
Running pt-query-digest on a slow query log, I get something like this:
# 1.2s user time, 10ms system time, 22.30M rss, 114.48M vsz
# Current date: Wed Oct 24 23:44:05 2012
# ...
2
votes
1answer
94 views
How to know what to investigate and how for a VERY slow stored procedure
I have inherited a large and slow stored procedure and it's giving me a nightmare:
I'm not a DBA, although I have some knowledge, but I don't know where to start to identify this bottleneck.
I have ...
3
votes
1answer
95 views
can sql generate a good plan for this procedure?
I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads ...
9
votes
6answers
2k views
Suddenly Slow Execution Plan for Stored Proc
I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, ...
3
votes
2answers
1k views
How to optimize this dynamic stored procedure
I have a stored procedure which is retrieving data using 20 tables.
Sample of the procedure:
CREATE PROCEDURE GetEnquiries
(
@EnquiryDate DATETIME = NULL
)
AS
...
8
votes
2answers
526 views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity.
One thing the DBA mentioned was I should ...
5
votes
3answers
402 views
Does SQL server optimize or pre-parse stored procedures?
Despite what DBAs will tell you ("use stored procedures because the server will optimize them thus they are faster than ad-hoc queries"), I have heard that SQL server does not actually optimize stored ...
4
votes
2answers
418 views
Fetch first 9 records and make one more record which will be as “Others” in MySQL
I am fetching records from one table with count of one field with other field as name.
I want only 10 records. out of which 9 records give me field and its count. but i want to show 10 record as ...
