| Author |
Topic |
|
natashanka_66
Starting Member
3 Posts |
 Posted - 2007-12-02 : 23:16:33
|
| Hello!I have a query with multiple table joints and where clauses. Tables are huge and query runs for a very long time. I need to run another query, which is exactly the same apart for one Where clause (first time price = "P", second time price = "S"). I use this for BIRT report, which means I need to have a result of there two queries at the same time. I can run them at the same time, but because they are both huge it takes a Long time. I wonder if I can make only one query, will it be faster? And if it will- what’s the best way to make it?Thank you for your help!Please let me know if it is not clear.Roughly my code:Select * FROM sales_item d join (select str_to_date('2007-09-30', '%Y-%m-%d') st_date) v join item_store f on f.item_no=d.item_no and f.store_id = d.store_id join item_sellprice_store a on f.item_no=a.item_no and f.store_id = a.store_id join item b on f.item_no=b.item_no join item_supplier c on b.item_no=c.item_no and b.supplier_no=c.supplier_no join item_sellprice_storegroup g on a.batch_id=g.batch_id join store k on f.store_id =k.store_id where d.store_id = '011' and (f.item_status ='A' or f.item_status ='O') and a.price_type='P' and a.effective_start_date < v.st_date < a.effective_end_date and d.sale_date = v.st_date order by d.category_code, a.sell_gst, a.item_no ; And the second one is exactly the same apart from highlighted line (it’s a.price_type='S') |
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2007-12-02 : 23:30:32
|
| is there any indexes on your table?first analyze which table is having clustered index and which is having non clustered index.try to put non clustered index on the column which is in the where clause i.e (store_id).defenetly it will make difference.VinodEven you learn 1%, Learn it with 100% confidence. |
 |
|
|
natashanka_66
Starting Member
3 Posts |
Posted - 2007-12-03 : 00:10:39
|
| No, no indexes. and it is client's database, so i can't change it... |
|
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2007-12-03 : 00:22:51
|
| you have to create indexes on your tables. or else forget about performance...VinodEven you learn 1%, Learn it with 100% confidence. |
|
|
|
natashanka_66
Starting Member
3 Posts |
Posted - 2007-12-03 : 00:32:22
|
| sorry, i was wrong, it does have indexes! how can i put index in where clause?thank you |
|
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2007-12-03 : 00:52:27
|
| i'm not saying to put the index on where clause....in where clause, there is condition like where my_column='something'on this my_column add a non clustered index. i think there are primary key on your table. because id columns are there.so it means already clustered index is there. now observe in which table the data is changing continuosly.and try to defragment the database once... run the script on your database--Script to automatically reindex all tables in a database DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN --PRINT " Re indexing " + @TableName DBCC DBREINDEX(@TableName,' ',90) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursorVinodEven you learn 1%, Learn it with 100% confidence. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-12-03 : 07:17:24
|
| >> join (select str_to_date('2007-09-30', '%Y-%m-%d') st_date) vWhat is going on here? This is most likely why your sql is so slow, it is extremely inefficient to join on expressions. Show us your *actual* code and we can help better.Also, if you a running a report twice, once for one value in a column and again for another, you should look into using groups on your report, and group on that column. Then, it will separate your report into two groups, one for each value, so that your report can return them all in one pass.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2007-12-03 : 08:59:30
|
quote:
join (select str_to_date('2007-09-30', '%Y-%m-%d') st_date) v
is this actually MySQL rather than SQL Server?Em |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-12-03 : 09:17:18
|
| Ah, yes, most likely. At first I thought it might be a UDF but you are probably correct.natashanka_66 -- this is a Microsoft SQL Server web site. MySQL is very, very different. You should ask for help at a MySQL specific forum.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
Two same queries with one difference