Indexes for database really make a different in query speed
March 1, 2012
Last time I have done the formatting workaround with the negative parenthesis. This time I have to optimize the speed with the sync of the InDesign Plugin in Mac called datalinker from teacup software. It is a database one-way sync program that does the price for you in InDesign Template Document. The price is done by my Flex and Java CMS program transferring the price from our provider with Excel sheet and translate each cell to sybase.
Originally, it takes 2 min to sync the value tag with one report of approx. 380 tags. It mean it will run the procedure 380 times per report. The reason behind the length time is because of the query is taken from a half millions rows table and require the store procedure to do 3 query, 2 select into, and format the way we want. So, I follow the sybase’s optimization recommendation and I have done is the following:
1.) Create Index for the query especially match to my “where” statement
2.) Update Stats of that big table. ( I guess the DBA will do a cron job in production but definitely not in my dev server)
3.) Drop the tempDb table asap when it is done.
Once I done all these, the time is gone from 2 min to 20 sec. So, indexes really matter. Also, Update statistic is a necessary for that table.
There are a few things I can work on, such as split the queries into 2 store procedures, make the query into a nest query. and etc ….