June 5, 2009 at 7:24 am
Hey everyone,
So our company is creating crystal reports based off of information we are pulling from our databases. I am running into a very big problem dealing with the time it takes to pull the amount of records we need to create this report. How our system of doing it is set up is as follows:
We have multiple tables in a database that have similar table names that we need to pull all records from (ex. wheel01, wheel02, wheel03). However not all these tables in the database are named in such a manner. Our process is as follows:
Using an ASP.NET C# web application we send the SQL server a statement asking for all table names that are LIKE %wheelXX%, that way we can get a list of just those tables. After that we store these table names into the application in an arrayList. From the application we read through the arrayList and send each table name as a parameter to a SQL stored procedure that pulls all the records we need from that single table. Unfortunately the next step is where things start to go haywire. We then execute an insert into statement that takes all the records returned from the stored procedure into a MS access temporary table (MasterTable) so that the aggregate of all the records pulled from the stored procedures are inside of this table. We have to use access in order to run our crosstab queries we have on all the data we get inside the master table (we are not using SQLServer2k5 and don't really have sufficient knowledge on writing them in SQL 2000). The whole process takes between 4-6 minutes. When the application is done executing we have about 362,000 records that are being written into access from all these wheelXX tables.
My questions are as follows:
1. Can anyone identify where the problem is occuring (I assume its most likely having to transfer these records into access)
2. What would you suggest be a proper way of doing this for the quickest results.
3. Is there anything else you can suggest.
We have been working on this project for the last 2 weeks trying multiple ways of pulling these records more efficiently and I just wanted to see what some people with some different/greater knowledge than myself could provide. Thanks in advance.
Regards,
Brent
June 5, 2009 at 8:26 am
If you are only using Access to do the cross tabs then I would suggest scrapping access and reworking the crosstab SQL using CASE statements if you are on SQL2000 or the PIVOT operator in SQL2005. Another option might be to link Access to the data rather than importing.
My 2p.
June 8, 2009 at 8:59 am
Brent, I have to agree with Allen. Unless there is data in the Access database you are using in your crosstabs, you would probably be best served doing as much as possible in SQL Server, and importing the minimun number of rows possible. If you post your table structure, the procedure, and some sample data, we can probably help you improve your situation.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply