July 18, 2010 at 7:04 am
Hi,
We have an SP which gets called during our form initialization. Currently this returns 70+ tables data and that data will be transferred to the client in the form of DataSet objects (over WCF framework).
Before calling the query, This query runs sequentially to
For the first time usage of the application (not during every startup of the application), this query returns data for all the tables. This data will be cached on client side (using IsolatedStorage) and from lateron some datetime fields will be passed to the query. These values will be compared on the server, and then data only for those tables for which date values do not match will be passed to the client. Irrespective of the situation, 70+ tables will be returned: first time all tables will have data and from second time onwards only few tables will have data and other tables are empty.
The SP processes each table sequentially and then returns the data. As such it is taking lot of time. All the tables are independent. Hence I am thinking if there is a way to fill those tables simulatenously.
Is it possible to group tables; filling them in separate SPs. And then calling all those SPs asynchronously from one main SP....?
Any ideas?
Thanks in advance
Aravind
July 18, 2010 at 4:22 pm
You need to determine EXACTLY what is taking the time
I don't see any evidence of that determination in your post
Without a clearly defined problem there is no point in thinking about a solution
Once it is proven to be SQL related - post some more detailed information
July 18, 2010 at 5:37 pm
ara_tech2004 (7/18/2010)
The SP processes each table sequentially and then returns the data. As such it is taking lot of time. All the tables are independent. Hence I am thinking if there is a way to fill those tables simulatenously.
Without knowing the "why" first thought that pop-up on my mind is: unusual design.
Have this process ever performed well? If the answer is yes then check what has changed since the last time it performed well.
ara_tech2004 (7/18/2010)
Is it possible to group tables; filling them in separate SPs. And then calling all those SPs asynchronously from one main SP....?
Everything and anything is possible but not knowing what the problem is suggesting a solution would be unprofessional.
ara_tech2004 (7/18/2010)
Any ideas?
Has somebody bothered in finetuning each single query?
Has somebody bothered in setting up an indexing strategy that may help queries perform better?
Has somebody bothered in revising the application design?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 18, 2010 at 10:59 pm
PaulB-TheOneAndOnly (7/18/2010)
ara_tech2004 (7/18/2010)
The SP processes each table sequentially and then returns the data. As such it is taking lot of time. All the tables are independent. Hence I am thinking if there is a way to fill those tables simulatenously.Without knowing the "why" first thought that pop-up on my mind is: unusual design.
Have this process ever performed well? If the answer is yes then check what has changed since the last time it performed well.
It may not be unusual design. I will explain what actually it is doing. The data that is returned by this SP is 'domain data'. When the SP is called first time, this data will be cached on client side. After that, everytime this SP is called, server checks whether the client has latest domain data or not. If the client has old data server will push fresh data. Part of the domain data has to be always fresh and hence this data will always be pushed.
ara_tech2004 (7/18/2010)
Is it possible to group tables; filling them in separate SPs. And then calling all those SPs asynchronously from one main SP....?Everything and anything is possible but not knowing what the problem is suggesting a solution would be unprofessional.
ara_tech2004 (7/18/2010)
Any ideas?Has somebody bothered in finetuning each single query?
Has somebody bothered in setting up an indexing strategy that may help queries perform better?
Has somebody bothered in revising the application design?
Indexes are setup. And each singe query was fine tuned. But as the application is ageing, domain data is also growing. Currently we are returning 71 tables where initially it was less than that number. Coming to revising application design - that is what I am trying to do now:-)
Initially I thought I would get this data async so that user does not need to wait for the application to be setup completely. But unfortunately it turned out that, all the initial controls need this data hence I am left with no choice.
So, I shifted my thoughts to DB side. I also have some thoughts on middleware: like creating an indexed view and then calling this view from middletier on a pre-determined time interval and then returning that data to the client. But business has to approve it (because as i already said some data has to be always fresh on the client side).
One other idea that I am thinking is....dividing one single SP into multiple SPs and then calling all those SPs in multiple threads from the middletier. But I am a bit worried about this design because of multi threading issues on WCF.
I Hope I explained the need and design thoughts fully.
I thank you everyone very much for the time you spent on this issue.
July 26, 2010 at 2:39 pm
The first thing I would recommend is to change from caching the 70+ table on startup to only read the table data in as need – just-in-time (when the user call for it). Startup will be much quicker and the user will only notice a slight delay the first time they need to access the data.
If you still experience some degradation on the initial population of a cache, you will need to integrate and analyze that particular query.
July 27, 2010 at 7:37 am
you cannot do what you ask (asynchronously return 70 data streams from a single sproc). so change your calling structure to hit 70 sprocs that each return their own set since all of the tables are independent. problem solved.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply