| Author |
Topic |
|
hon123456
Starting Member
1 Post |
 Posted - 2010-07-10 : 02:46:32
|
| Dear all,I have the query:SELECT scode, sstockcode, sindate, ssupcode, ssupstock, ssupinvno, sinputno, sweight, slength, slabor, sjmqty, sjaqty, sjawgt, sjjwgt, sjpdia, sggoldp, scost, sprice, location, sxferdate, sxferref, ssaledate, ssaletime, ssalememo, ssaleprice, sstaff, stracode, sguicode, barcode, remark, [bulk], sinputseq, returndate, loc, mstaff, sp_type, sstockcode + '-' + ssupcode as stockcode FROM stockWhich takes 11 seconds to finished. And the record number is 175000.What can I do to make it fast to 1 second. I have made many index field, but it does not help? should I include all the fields in the select fields to create an cluster index to imporve the speed?Or there are other methods that can speed up query. I need to showall the records in UI of my program. |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-07-10 : 03:19:26
|
| i'm 100% sure that in your bussines process you do not need to retrieve all 175.000 rows - especially not in 1 second. i'm aware that this is a stock table, but usually users can comprehend up to 20 information - 20 rows. either is something wrong with the UI or application that needs to retrieve and show all the rows but not even in 1 second. just imagine, even if a user starts reading the lines, by the time he/she reaches to 20th row, you query will be finished.So rethink what you wish to retrieve and show in 1 second. usually retrieving takes a lot of time. |
 |
|
|
|
|
|
How to make the query faster