CODEWITHSUNDEEP
mysqlsqlquery-performance
kamil kunt
kamil kunt

Reputation: 1

Sql Stored Procedure Speed Problems

I have a stored procedure, it is for getting datas on 10 table between two dates. It is return much than 1000 rows. When I call this procedure on website, sometimes it is return "website not respons" error. How should I optimize this problem.

BEGIN
SELECT * FROM banka_cariodeme WHERE (vade_tarihi >= p_dt_bas) AND (vade_tarihi <= p_dt_bts) ORDER BY vade_tarihi DESC;
SELECT * FROM banka_caritahsilat WHERE (vade_tarihi >= p_dt_bas) AND (vade_tarihi <= p_dt_bts) ORDER BY vade_tarihi DESC;
SELECT * FROM kasa_cariodeme WHERE (vade_tarihi >= p_dt_bas) AND (vade_tarihi <= p_dt_bts) ORDER BY vade_tarihi DESC;
SELECT * FROM kasa_caritahsilat WHERE (vade_tarihi >= p_dt_bas) AND (vade_tarihi <= p_dt_bts) ORDER BY vade_tarihi DESC;
SELECT * FROM cek_cariodeme WHERE (vade_tarihi >= p_dt_bas) AND (vade_tarihi <= p_dt_bts) ORDER BY vade_tarihi DESC;
SELECT * FROM cek_caritahsilat WHERE (vade_tarihi >= p_dt_bas) AND (vade_tarihi <= p_dt_bts) ORDER BY vade_tarihi DESC;
SELECT * FROM pos_cariodeme WHERE (vade_tarihi >= p_dt_bas) AND (vade_tarihi <= p_dt_bts) ORDER BY vade_tarihi DESC;
SELECT * FROM pos_caritahsilat WHERE (vade_tarihi >= p_dt_bas) AND (vade_tarihi <= p_dt_bts) ORDER BY vade_tarihi DESC;
SELECT * FROM senet_cariodeme WHERE (vade_tarihi >= p_dt_bas) AND (vade_tarihi <= p_dt_bts) ORDER BY vade_tarihi DESC;
SELECT * FROM senet_caritahsilat WHERE (vade_tarihi >= p_dt_bas) AND (vade_tarihi <= p_dt_bts) ORDER BY vade_tarihi DESC;
END

Upvotes: 0

Views: 113

Answers (4)

Kevin
Kevin

Reputation: 2243

Okay, there seem to be a few main possibilities in my mind:

Possibility #1: Missing Indexes

Imagine your table doesn't have any indexes on that date field. So if you want to find records that match your query... the SQL engine has to look through the full table to find what it's looking for! The idea behind an index is so that it can quickly Binary-Find the records that match

Possibility #2: Indexes Not Used

This one's a bit tougher to diagnose. You'll have to use whatever query analysis tools MySql gives you (I'm not as familiar with that one) to figure it out. But, basically, if you've got a query where the engine things the index will return too many rows to be worth it, it'll just use the main table anyways. For instance, imagine doing a LastName != "Thomas". Why would it use an index to figure out 99% of the rows are going be returned, and then try to match it up against the main table? No - it'd just use the main table to begin with.

Possibility #3: Too Much Data

It could be that the problem is that its simply returning a lot of data. You've got SELECT * in there - it's going to return every single column in the table. Not only can this be bad design (what if a column gets added/inserted?) but it means that it's having to pass back data that might not even be getting used.

EDIT: Possibility #4: Need A Combined Index

I misread part of the statement the first time around, and assumed the p_dt_bas and p_dt_bts values were variables from the front end and not additional columns in the table. In that case, there's another possibility: that it can't use any existing indexes because they don't include all the information. After all, if you've got a query of ColA=ColB and separate indexes on each columns... it can't use the indexes. After all, an index is just a sort of mini-table of: {indexed column(s), pointer to main table}. So using an index of {ColA, pointer} doesn't help out finding values where ColA=ColB. You'd need to have ColB in the same index. Likewise, for a query of A < B < C, you'd need an index with all three columns. IMPORTANT NOTE: queries like this will still not achieve Binary-Find performance. But it'll be able to scan through just the index - not a full table scan. Depending on how many columns your tables have, this is still a pretty important speed-up.

Upvotes: 2

Jernej
Jernej

Reputation: 11

EXPLAIN EXTENDED SELECT * FROM banka_cariodeme WHERE (vade_tarihi >= p_dt_bas) AND (vade_tarihi <= p_dt_bts) ORDER BY vade_tarihi DESC;

Would be a good start and would give some information about database and indexes...

Upvotes: 0

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

Make sure you have an index on p_dt_bas and p_dt_bts. You can also use the BETWEEN operator in your where clause instead of doing two comparisons.

BEGIN
    SELECT * FROM banka_cariodeme 
        WHERE vade_tarihi between p_dt_bas AND p_dt_bts 
        ORDER BY  vade_tarihi DESC;
    SELECT * FROM banka_caritahsilat 
        WHERE vade_tarihi BETWEEN p_dt_bas AND p_dt_bts 
        ORDER BY  vade_tarihi DESC;
    SELECT * FROM kasa_cariodeme 
        WHERE vade_tarihi BETWEEN p_dt_bas AND p_dt_bts 
        ORDER BY  vade_tarihi DESC;
    SELECT * FROM kasa_caritahsilat 
        WHERE vade_tarihi BETWEEN p_dt_bas AND p_dt_bts 
        ORDER BY  vade_tarihi DESC;
    SELECT * FROM cek_cariodeme 
        WHERE vade_tarihi BETWEEN p_dt_bas AND p_dt_bts 
        ORDER BY  vade_tarihi DESC;
    SELECT * FROM cek_caritahsilat 
        WHERE vade_tarihi BETWEEN p_dt_bas AND p_dt_bts 
        ORDER BY  vade_tarihi DESC;
    SELECT * FROM pos_cariodeme 
        WHERE vade_tarihi BETWEEN p_dt_bas AND p_dt_bts 
        ORDER BY  vade_tarihi DESC;
    SELECT * FROM pos_caritahsilat 
        WHERE vade_tarihi BETWEEN p_dt_bas AND p_dt_bts 
        ORDER BY  vade_tarihi DESC;
    SELECT * FROM senet_cariodeme 
        WHERE vade_tarihi BETWEEN p_dt_bas AND p_dt_bts 
        ORDER BY  vade_tarihi DESC;
    SELECT * FROM senet_caritahsilat 
        WHERE vade_tarihi BETWEEN p_dt_bas AND p_dt_bts 
        ORDER BY  vade_tarihi DESC;
END

Upvotes: 0

derek.wolfe
derek.wolfe

Reputation: 1116

Adding an index to the vade_tarihi columns as Peter said above would help. Adding indexes to the p_dt_bas and p_dt_bts columns would also be beneficial.

Is it necessary to pull all of the columns in those tables? Selecting just the columns that are needed (if any are not) may help the speed as well since this would reduce the overall size of the query return.

If the query is still slow after, partitioning the tables by the vade_tarihi column may also be something to consider. This would allow the query optimizer to only look as subsets of data that are relevant to the query.

If those options do not work, consider allowing the db to use more ram for caching if you can.

Upvotes: 0

Related Questions