A question of performance 1

[innmedia]

Our current solution uses an Access front end. Selections are made in the Access form that becomes the criteria for SQL statements (action statements, not selects).

The question is, is the performance with this approach any different, or much different, than coding it up as stored procedures. When Access sends the actions SQL to SQL Server I assume it is running at the server, not having anything to do with the client machine?

The SQL makes tables in SQL Server, based on values found in other SQL Server tables. A front end is needed because there is alot of things a user has to select - which values to process, sort orders, etc.

I could somehow code this up into stored procedures but there are so many selections that it is difficult to get this much coding out of VBA and into T-SQL.

But should I do this? Would the performance improve?

Typically million of records are processed at a time. It takes a while. The process pretty much is to take tables with millions of records and not many fields, and turn it into tables with less records, but much more fields. The new fields are derived from the actual data points in the original data.
So if there are two fields, color and fruit, then a number of new fields are created from this, such as: apple_yellow, apple_red, apple_green, melon_orange, melon_green, etc.

I have built the process to handle making multiple new tables for when the 1024 field limit is popped. There are other complex things going on in the VBA. Field naming is one - is it apple_green, or green_apple? The user decides via selections on the Access form.

There are over 3000 lines of VBA that would have to be converted into a stored procedure. Any ideas if this is worth it?

Thanks!

 

[SQLSister]

If millions of records are involved almost certainly a stored procedure would be faster.

If you are doing inserts using an Access query, you do not receive the beneifts of stored procedure executioin plan reuse, and you are going through an extra translation step of using the access jet engine as well as the SQL server.

However it strikes me that your whole process in inefficent. Generally it is a bad idea to reate new tables at run time. Also you are converting from a nice relational model to a non-relational model with millions of records. Why are you doing this? Why not simply query the tables as they are structured or one time change the structure to what you want? Rather thatn creating new tables, shouldn;t you be using cross-tab queries? Then you wouldn;t be slowing things down by having all those transactions to process.

Questions about posting. See faq183-874

 

[innmedia]

Hi SQL Sister,

The process of altering the data structure is the client's requirement. They end up using the data in SPSS - a statistical package. They need the data in the the process creates it, although I don't get into that side of things.

I have wondered about the crosstab, cut can it handle field renaming, sorting, and more? I don't know if it can create the fields in the way the client wants.

picture two field, colors and fruit. Colors has red, green, yellow. Fruit has apple, banana, and grape. Here is the combinations that can be made.

apple-yellow
apple-green
apple-red
banana-yellow
banana-green
banana-red
grape-yellow
grape-green
grape-red

But that's not all. Then they can decide the sort order. this is based on the values found at the intersection of these items in the original table. For example, the original table has a value in another field where fruit=apple and color=red. These values can be sorted up, down or following a custom pattern a little deep to describe here.

Then they can choose the order of how the fields are created in the table: all the apples, then the bananas, then the grapes; OR all the reds, then all the yellows, then all the greens.


See how complex this gets. Can I recreate all of this with stored procs?

 

[SQLSister]

Yes you should be able to create all that with sps. Given the complexity you may end up using dynamic SQL which is loses some of the performance value of sp's though. Still it should be better than going through the Access jet engine.

If they just need the data that way but it doesn't matter to their other application if it is a query or a table, then you might be able to get away with using crosstabs instead of tabbles. I would suggest a long talk to the SPSS guru on how you together can make this work in the most efficient fashion.

It is my opinion that spending a week or on so at the client and with some SPSS experts to truly look at the possible different architecture mixes and design possiblities and how you can most efficently provide data in a form that they can process it owuld be the most efficent way that you can fix the performance problem. All the players in this game need to meet and discuss the actual requirements.

You may find that they simply told you the easiest way for them to process the data (or the only way they were aware of), but if it makes your side so inefficient, it might be worth the time to investigate if there are other ways they can receive then data that might mean a slight change in their process but a big gain in efficiency.

They also may need to be educated on the problems with the current approach. Is there any flexibility at all they are willing to sacrifice for efficiency?

At any rate a week or so of genuine discussion and requirements development and redesign may save you much more development time as you go on. It seems to me you are operating blind right now and might develop a fix that won't work for their situation or develop a less than optimum fix because they didn't realize a change on thier part could improve the overall system.

Questions about posting. See faq183-874

 

[innmedia]

SQL Sister,

yeah, A talk with the client is a good idea. Thanks for thinking it though.

 

[SQLSister]

I've talked to a lot of clients through the years and often when they realize the limitations they put on you are what is causing the problem, why magically the limitations aren't so important anymore.

And I've also learned that when systems interface, there is always more than one way to accomplish something but it involves a trade-off in one or the other of the systems. Trick is to understand all the systems that work together so thoroughly that you can balance the trade-offs to get the best overall system performance. The only way to do that is to work closely with the experts in the systems you are not expert in. They aren't expert in your system either and probably don't realize the problem that is being created on your end.

I know here, where our front end is .Net, we have had things that the .Net programmers didn't want to go to the extra effort of doing until they understood why it would be slow to use the database. And there were things that they were doing that I was able to show them a faster way to accomplish the same thing through a stored procedure. But if we had been isolated then we never would have made these changes and the overall system would work less efficiently.

Questions about posting. See faq183-874