Manipulating Datasets/Tables

[billcoupe]

I posted this in the Database development forum, but, got to thinking that the folks here might actually have a better handle on this.

I've been charged with converting a number of stored procedure (SP) type SQL extractions to run at the client instead of at the server.

These SP's run several dozen separate SQL statements, creating a number of temp tables that are later joined/updated/combined to yield a final dataset that's applied to an existing Crystal report.

I'm looking for a way to either, direct the SP's to run on the client instead of on the server (probably not possible), or, a way to emulate the temp table and joins/updates/combines with datatables within a strongly typed dataset.

Any ideas? I know that using stored procedures is really the 'recommended' best practice, the issue here is the processor burden on the server. Running the existing stored procedures bogs the server such that other applications can become erratic.

Thanks,

-Bill

[Qualm]

You can't "direct the stored procedures to run on the client", so forget that idea. Stored Procedures run on the server engine.

Leaving aside the question of whether it would be cheaper to upgrade the server than to reproduce in vb.net what these stored procedures do ...

There are quite a few ways you could duplicate what a stored procedure does and make it all run on the client side.

One option to consider is pulling all the data you want to work with out and temporarily writing it to local database files, say an Access .mdb - and then doing the joins/updates/combines on that file. That would probably be a whole lot easier than trying to create an entire virtual database structure using in-memory datasets/datatables.

[billcoupe]

Qualm -

Thanks for your input. The biggest problem is the cost of the 'upgrade'. The cost is in excess of 200K as the company is not in control of the actual hardware, only the data.

Believe me, I suggested they try the upgrade route, it's not an option at this time.

I'll play around with the Access idea as I had tossed the idea around, but thought that possibly there must be a faster, easier way than that.

It sure beats the thousand or so lines of code it would take to process the tables an build the final result table!!

Thanks again.

-Bill

[nemaroller]

You need to fill in a few areas on the requirements of this 'project' and its key goals.

How often does this 'crystal' report get run?

Is the database able to be copied locally?

etc...


If its done monthly, and you're going to end up pulling all the data anyway, could you not simply copy the database locally, and create a stored procedure that joins all this stuff?

This way you have all the data, its running on a local machine, and it gives you the data for the report.

If the report is done on the fly at any time, and that's just not feasible: Is the performance because database maintenance is lacking, or the stored procs used to call the data are not very efficient?

Would limiting the report to be run at a specified time during off-peak hours help?

Btw, Yes, you could simulate this using ADO.NET and it might require at the most 500- 1,000 lines... but if it takes a week to write, it takes a week. Nothing more nothing less. My problem with that would be maintainability if the database procs get changed.

[billcoupe]

Originally posted by nemaroller

If the report is done on the fly at any time, and that's just not feasible: Is the performance because database maintenance is lacking, or the stored procs used to call the data are not very efficient?

Would limiting the report to be run at a specified time during off-peak hours help?

Btw, Yes, you could simulate this using ADO.NET and it might require at the most 500- 1,000 lines... but if it takes a week to write, it takes a week. Nothing more nothing less. My problem with that would be maintainability if the database procs get changed.

Nemaroller -

Interesting that you should suggest this. The reports now are scheduled for 'off peak' hours and run only once a month. They're needed daily in some cases and weekly in others, in some cases they might be run several times a day.

One of the problems is that the database is many tables, 100's of thousands of records per table in many cases and the data required is not readily available 'in' the data, each of the SP's run dozens of SQL's and one is called repeatedly for each person, for each time period (that period could be hours, days, weeks).

I know I can write it and I'll probably experiment a little with the Access idea, but the more I look at it, the more I'm leaning towards simply extracting all the data I need and then processing it into the 'result' dataset to pass into Crystal.

The maintainability isn't really an issue as if these tables change it will be a major 'system change' by the outside vendor, everything will have to change anyway.

Thanks again for your thought here.

-Bill

[Edneeis]

If you get it to a dataset once you can persist it to xml to avoid future trips to the server until the next month. If the data is a lot then it will be a bit slow to load it back and forth from xml. Also if its an option you should run the Index Tuning Wizard on all of the queries in the stored procedures. We had some intense sps that the tuning wizard sped up by more than 80% and it definately helped with the cpu power too.