If you have 3 tables with over 10,000 records each and you have to build a recursive function with this 3 tables link, and you expect to have in return around 200 records, What will you do? "The website is intranet host"
Query the sql server multiple times or load the whole data once and play only at the web server level. "Any other suggestion".
The Sql server is a very high performance machine but the web server is not.
What is the best way to go?
I believe load the data once in cache and perform the operation. once your operation will complete, clear the cache.
onething why you need to build recursive function with datbase tables ? is there any special requirement ?
Also try to avoid using left joins to link the 3 tables instead use inner join. Create proper index on tables also.
If you have 3 tables with over 10,000 records each and you have to build a recursive function with this 3 tables link, and you expect to have in return around 200 records, What will you do? "The website is intranet host"
Query the sql server multiple times or load the whole data once and play only at the web server level. "Any other suggestion".
The Sql server is a very high performance machine but the web server is not.
What is the best way to go?
Lets say what im trying to do is once i have a customer order for a particular part. i have to find the demand and supplies in order to build that part, and the way the database is structure i believe making a recursive function is my best shot. the fact that we have SQL 2000 does not support CTE.
I think you can do this thing by joining/ linking tables. no need for creating recursive functions even if structure is not well defined. It's only my thoughts no need to follow this way as you are the owner/developer of application.
Anyways whatever the solution you will find just share with us.
Lets say what im trying to do is once i have a customer order for a particular part. i have to find the demand and supplies in order to build that part, and the way the database is structure i believe making a recursive function is my best shot. the fact that we have SQL 2000 does not support CTE.
Well i did what you said, load the data in cache, work with it and then delete it and when down from around 5 minutes to 1:30 min.
about your last post i am the owner/develop of this application but not the database structure i mean in part, that database is for a ERP System that we have im just building separate tools to accomplish specifics tasks, so about the database structure i cannot do too much, but let me tell you, i am most happy now.
thanks.
I think the bottom line is, never do multiple request to sql server, no matter how big is your result set bring the data to cache and get what you want, get rid of the rest.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.