I hv scheduled task for reload in morning earlier it was taking hardly 5 mins to reload but now its taking 2 hours for reload what cud be the problem plz suggest.
i checked the log files but could not found exact problem
You could schedule a trace to run on the SQL Server to allow you to analyse the locks and queries run against your target table.
Your document log example doesn't suggest the query is taking a long time, however you could look at adding indexes to the table. Here's some other things you could try ...
- Remove the CASE statements, pull through the source field as-is and add a preceding load with ApplyMap to modify these fields instead. This makes QV do the work and not your SQL server.
- Perhaps a GROUP BY statement may work better than DISTINCT, especially if you have indexes.
- Try adding a NOLOCK hint to your query if you can live with uncommitted transactions.
If nothing else works, then you could write a scheduled task in SQL (SSIS/DTS maybe) that dumps your desired data into a new table for you to query instead, thus negating any query contentions.
