Strings in sql queries
-
Dear Friends,
I have a set of sql queries in which i use the string concatenation and left and right functions for mapping the data in my database. It works fine but takes atleast 20 mins for mapping the data. My Database has records of nearly 5 lakhs and data which I get nearly some 10,000 daily. Day by day this mapping process is consuming more part of my work time. So pls suggest a best method in which I can reduce the query execution time using the same concatenation and left & right operations.
Waiting for your earlier reply.
Thanks & regards
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font] -
What is blocking you from automating this process and not having to intervene at all?
-
If you were to test the 10,000 updates on an empty table, would it still take 20 minutes? What I suspect that it is not the string functions that are slowing down the process but indexes, constraints and possibly triggers. If you run 10,000 updates on an empty table and it takes 20 minutes, then begin to address and optimize the string functions. If the 10,000 updates are fast, (less than a minute ), then look for excessive indices, constraints or triggers (one slow process I researched once had over 10 indices, about 17 referential integrity constraints and half a dozen update/insert triggers, none of which the system owner wanted or could give up).
p.s. Please remember that most of us don't know what a lakh is; i assumed it is a million or a billion
-
FYI 1 lak is 100 K.100K should not take long to process.
-
Try using an Index on this column.
Also, if this table has a lot of inserts/deletes...i suggest u do a re-index every night.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply