Pass-Thru Query or Stored Procs Bagger 12.09.2006 05:13:02 I'm working with someone trying to revamp their reporting system. They are currently using SQL Server 2000 as the backend and Access for doing their reporting. The problem is that they are building these huge complicated pass-thru queries, and often stringing several queries together to get the dataset they need for their report. The analysts know Access pretty well and are comfortable with it, but the performance is just pretty bad. A few of the reports take more than 10 minutes to run. Now they aren't dealing with a small database. A couple of the tables have nearly a hundred million records in them. Now, I can't ask for anyone to tell me whether a specific set of queries, re-written as a stored procedure would be a major improvement or not, since I would have to give you a boatload of info on the tables, their relationships, and all the index info, among other things before you could really give a good answer. Since that's not very practical, what I'd like to find is more general information about pass-thru queries vs. stored procedures. What benefits can be gained from using one vs. the other. What is the recommended method of using Access as a reporting tool for SQL Server? What I'm trying to do here is to see if there is justification for continuing to use Access or not, and whether they continue to use it, or something else, whether stored procedures will be necessary to achieve the performance they want. If it looks like stored procedures are necessary, I'll need to make the case for them to hire someone to create those procedures. So this is about finding the info necessary to build a case for that, or for something else if the findings lead elsewhere.

Re: Pass-Thru Query or Stored Procs "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 12.09.2006 05:40:34 Once compiled, there is no real difference between them if the underlying code is the same. You will have a difference if you are sending the same query a few hundred or a thousand times per second but for a job which is taking 10 minutes, I don't see what difference this could make. However, the best way to be sure would be to make the test; so why don't you grab one of these build sql string, put it in a stored procedure and see what time this SP take to run? This doesn't mean that you cannot optimize your queries (if it is possible to do so, of course). However, you don't provide any information on this regard. For example, does the fact that some of these tables have some hundred millions of rows means that all these rows must be read for generating a report or if only a few rows are required? You can always hire someone but in my opinion, the first thing to do would be to buy some good books on optimizing SQL-Server and queries and the second thing to do would be to take a serious look at the generated query's plans. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "Bagger" <Bagger[ at ]discussions.microsoft.com> wrote in message news:7C1EF3D5-DEE2-4A1B-93FB-FD0D184DA9D6[ at ]microsoft.com... [Quoted Text] > I'm working with someone trying to revamp their reporting system. They > are > currently using SQL Server 2000 as the backend and Access for doing their > reporting. The problem is that they are building these huge complicated > pass-thru queries, and often stringing several queries together to get the > dataset they need for their report. The analysts know Access pretty well > and > are comfortable with it, but the performance is just pretty bad. A few of > the reports take more than 10 minutes to run. > > Now they aren't dealing with a small database. A couple of the tables > have > nearly a hundred million records in them. Now, I can't ask for anyone to > tell me whether a specific set of queries, re-written as a stored > procedure > would be a major improvement or not, since I would have to give you a > boatload of info on the tables, their relationships, and all the index > info, > among other things before you could really give a good answer. > > Since that's not very practical, what I'd like to find is more general > information about pass-thru queries vs. stored procedures. What benefits > can > be gained from using one vs. the other. What is the recommended method of > using Access as a reporting tool for SQL Server? What I'm trying to do > here > is to see if there is justification for continuing to use Access or not, > and > whether they continue to use it, or something else, whether stored > procedures > will be necessary to achieve the performance they want. If it looks like > stored procedures are necessary, I'll need to make the case for them to > hire > someone to create those procedures. So this is about finding the info > necessary to build a case for that, or for something else if the findings > lead elsewhere.

Re: Pass-Thru Query or Stored Procs Bagger 12.09.2006 06:31:01 Ok, I understand what you're saying, and I agree that it won't make much difference if we just slap the queries into a sproc. The queries are crunching through a lot of data, and do things like using case statements within the sql to modify the values as it goes. Now, we're definitely not running these queries multiple times, but we are running multiple queries with each one based on the output of the previous one. So, the first one is a pass-thru query that returns a large dataset which then gets munched on by all the subsequent queries. Might that large data transfer cause some of the slowdown that we're seeing? Could it be faster to do all the processing within a sproc and only return the final dataset? I haven't worked with SQL Server in quite a while and I think I've probably forgotten a lot of what I learned about optimization, so I'll definitely need to brush up on that before attempting to create an optimized sproc to handle one of their reports. Guess there's no real way around that though :( "Sylvain Lafontaine" wrote: [Quoted Text] > Once compiled, there is no real difference between them if the underlying > code is the same. You will have a difference if you are sending the same > query a few hundred or a thousand times per second but for a job which is > taking 10 minutes, I don't see what difference this could make. > > However, the best way to be sure would be to make the test; so why don't you > grab one of these build sql string, put it in a stored procedure and see > what time this SP take to run? > > This doesn't mean that you cannot optimize your queries (if it is possible > to do so, of course). However, you don't provide any information on this > regard. For example, does the fact that some of these tables have some > hundred millions of rows means that all these rows must be read for > generating a report or if only a few rows are required? > > You can always hire someone but in my opinion, the first thing to do would > be to buy some good books on optimizing SQL-Server and queries and the > second thing to do would be to take a serious look at the generated query's > plans. > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF > > > "Bagger" <Bagger[ at ]discussions.microsoft.com> wrote in message > news:7C1EF3D5-DEE2-4A1B-93FB-FD0D184DA9D6[ at ]microsoft.com... > > I'm working with someone trying to revamp their reporting system. They > > are > > currently using SQL Server 2000 as the backend and Access for doing their > > reporting. The problem is that they are building these huge complicated > > pass-thru queries, and often stringing several queries together to get the > > dataset they need for their report. The analysts know Access pretty well > > and > > are comfortable with it, but the performance is just pretty bad. A few of > > the reports take more than 10 minutes to run. > > > > Now they aren't dealing with a small database. A couple of the tables > > have > > nearly a hundred million records in them. Now, I can't ask for anyone to > > tell me whether a specific set of queries, re-written as a stored > > procedure > > would be a major improvement or not, since I would have to give you a > > boatload of info on the tables, their relationships, and all the index > > info, > > among other things before you could really give a good answer. > > > > Since that's not very practical, what I'd like to find is more general > > information about pass-thru queries vs. stored procedures. What benefits > > can > > be gained from using one vs. the other. What is the recommended method of > > using Access as a reporting tool for SQL Server? What I'm trying to do > > here > > is to see if there is justification for continuing to use Access or not, > > and > > whether they continue to use it, or something else, whether stored > > procedures > > will be necessary to achieve the performance they want. If it looks like > > stored procedures are necessary, I'll need to make the case for them to > > hire > > someone to create those procedures. So this is about finding the info > > necessary to build a case for that, or for something else if the findings > > lead elsewhere. > > >

Re: Pass-Thru Query or Stored Procs <david[ at ]epsomdotcomdotau> 12.09.2006 10:32:53 Firstly, those 10 minute reports replaced something that took 4 days to calculate :~). If your people are pretty good at Access, moving to a platform that can do the same report an 10 seconds may be a step back to 4-day reports (or to 3 month project queues.) You need to look at the slow reports and see where the delay is. Is it only when you string the queries together? Is it only when you link the query to a report? If you have one slow passthrough query, you may need to look at the server table design. If you have one slow query on linked tables, you may need to look at using pass-through queries instead of linked tables. If you have a query that is slow only when you join two queries, you may need to look at using a server view - or you may just need to change some of the joins. If you have a query that is slow only when it is linked to a report, you may need to modify the report. Have you asked the users why the report is slow? There is something odd about your question here: if the users are good with access, and good enough with sql server to write pass-through queries, why are they asking you for help with their reports? Access is still a very good report writing tool, as good as or better than Chrystal, but in a much smaller niche, more accessible and in a different niche than Report Services. You won't be able to make the reports any faster by switching to Crystal or Report Services: they aren't any faster at rendering reports, and they connect directly (pass- through) to sql views. You can get drill-down reports by switching platforms, but drill-down reports are for people who don't know what they want, including where reports are designed by people who don't have good communication with the end user. If you've already got in-house development, and these are in-house reports, you won't get any value from switching platforms. (david) "Bagger" <Bagger[ at ]discussions.microsoft.com> wrote in message news:7C1EF3D5-DEE2-4A1B-93FB-FD0D184DA9D6[ at ]microsoft.com... [Quoted Text] > I'm working with someone trying to revamp their reporting system. They are > currently using SQL Server 2000 as the backend and Access for doing their > reporting. The problem is that they are building these huge complicated > pass-thru queries, and often stringing several queries together to get the > dataset they need for their report. The analysts know Access pretty well and > are comfortable with it, but the performance is just pretty bad. A few of > the reports take more than 10 minutes to run. > > Now they aren't dealing with a small database. A couple of the tables have > nearly a hundred million records in them. Now, I can't ask for anyone to > tell me whether a specific set of queries, re-written as a stored procedure > would be a major improvement or not, since I would have to give you a > boatload of info on the tables, their relationships, and all the index info, > among other things before you could really give a good answer. > > Since that's not very practical, what I'd like to find is more general > information about pass-thru queries vs. stored procedures. What benefits can > be gained from using one vs. the other. What is the recommended method of > using Access as a reporting tool for SQL Server? What I'm trying to do here > is to see if there is justification for continuing to use Access or not, and > whether they continue to use it, or something else, whether stored procedures > will be necessary to achieve the performance they want. If it looks like > stored procedures are necessary, I'll need to make the case for them to hire > someone to create those procedures. So this is about finding the info > necessary to build a case for that, or for something else if the findings > lead elsewhere.

Re: Pass-Thru Query or Stored Procs "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 12.09.2006 15:35:51 « So, the first one is a pass-thru query that returns a large dataset which then gets munched on by all the subsequent queries. Might that large data transfer cause some of the slowdown that we're seeing? Could it be faster to do all the processing within a sproc and only return the final dataset? » Yes, of course. However, it's impossible to tell you by how much you will see an increase in performance for running all this directly on the SQL-Server; you will have to make the test yourself. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "Bagger" <Bagger[ at ]discussions.microsoft.com> wrote in message news:5BA98355-E324-4EC3-A0C2-F64E98F67F48[ at ]microsoft.com... [Quoted Text] > Ok, I understand what you're saying, and I agree that it won't make much > difference if we just slap the queries into a sproc. The queries are > crunching through a lot of data, and do things like using case statements > within the sql to modify the values as it goes. > > Now, we're definitely not running these queries multiple times, but we are > running multiple queries with each one based on the output of the previous > one. So, the first one is a pass-thru query that returns a large dataset > which then gets munched on by all the subsequent queries. Might that > large > data transfer cause some of the slowdown that we're seeing? Could it be > faster to do all the processing within a sproc and only return the final > dataset? > > I haven't worked with SQL Server in quite a while and I think I've > probably > forgotten a lot of what I learned about optimization, so I'll definitely > need > to brush up on that before attempting to create an optimized sproc to > handle > one of their reports. Guess there's no real way around that though :( > > "Sylvain Lafontaine" wrote: > >> Once compiled, there is no real difference between them if the underlying >> code is the same. You will have a difference if you are sending the same >> query a few hundred or a thousand times per second but for a job which is >> taking 10 minutes, I don't see what difference this could make. >> >> However, the best way to be sure would be to make the test; so why don't >> you >> grab one of these build sql string, put it in a stored procedure and see >> what time this SP take to run? >> >> This doesn't mean that you cannot optimize your queries (if it is >> possible >> to do so, of course). However, you don't provide any information on this >> regard. For example, does the fact that some of these tables have some >> hundred millions of rows means that all these rows must be read for >> generating a report or if only a few rows are required? >> >> You can always hire someone but in my opinion, the first thing to do >> would >> be to buy some good books on optimizing SQL-Server and queries and the >> second thing to do would be to take a serious look at the generated >> query's >> plans. >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: http://cerbermail.com/?QugbLEWINF >> >> >> "Bagger" <Bagger[ at ]discussions.microsoft.com> wrote in message >> news:7C1EF3D5-DEE2-4A1B-93FB-FD0D184DA9D6[ at ]microsoft.com... >> > I'm working with someone trying to revamp their reporting system. They >> > are >> > currently using SQL Server 2000 as the backend and Access for doing >> > their >> > reporting. The problem is that they are building these huge >> > complicated >> > pass-thru queries, and often stringing several queries together to get >> > the >> > dataset they need for their report. The analysts know Access pretty >> > well >> > and >> > are comfortable with it, but the performance is just pretty bad. A few >> > of >> > the reports take more than 10 minutes to run. >> > >> > Now they aren't dealing with a small database. A couple of the tables >> > have >> > nearly a hundred million records in them. Now, I can't ask for anyone >> > to >> > tell me whether a specific set of queries, re-written as a stored >> > procedure >> > would be a major improvement or not, since I would have to give you a >> > boatload of info on the tables, their relationships, and all the index >> > info, >> > among other things before you could really give a good answer. >> > >> > Since that's not very practical, what I'd like to find is more general >> > information about pass-thru queries vs. stored procedures. What >> > benefits >> > can >> > be gained from using one vs. the other. What is the recommended method >> > of >> > using Access as a reporting tool for SQL Server? What I'm trying to do >> > here >> > is to see if there is justification for continuing to use Access or >> > not, >> > and >> > whether they continue to use it, or something else, whether stored >> > procedures >> > will be necessary to achieve the performance they want. If it looks >> > like >> > stored procedures are necessary, I'll need to make the case for them to >> > hire >> > someone to create those procedures. So this is about finding the info >> > necessary to build a case for that, or for something else if the >> > findings >> > lead elsewhere. >> >> >>

Re: Pass-Thru Query or Stored Procs "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 12.09.2006 16:12:18 VBA is notoriously slow, so the simple fact of locally scanning a big recordset in VBA will take time. Encoding and decoding a resulting to transmit it/receive it over the wire also take time. A well optimized SP will perform ten, hundred or thousand of times faster than a lousy written one. Finally, there are hardware considerations like the number of cores and the type of I/O on the server. If you want a number, I won't be surprised if you see an increase in performance of 90% and above. However, if you don't reach this number, don't come back here to complaint. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> wrote in message news:uaCcmEo1GHA.4116[ at ]TK2MSFTNGP02.phx.gbl... [Quoted Text] >« So, the first one is a pass-thru query that returns a large dataset > which then gets munched on by all the subsequent queries. Might that > large > data transfer cause some of the slowdown that we're seeing? Could it be > faster to do all the processing within a sproc and only return the final > dataset? » > > Yes, of course. However, it's impossible to tell you by how much you will > see an increase in performance for running all this directly on the > SQL-Server; you will have to make the test yourself. > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF > > > "Bagger" <Bagger[ at ]discussions.microsoft.com> wrote in message > news:5BA98355-E324-4EC3-A0C2-F64E98F67F48[ at ]microsoft.com... >> Ok, I understand what you're saying, and I agree that it won't make much >> difference if we just slap the queries into a sproc. The queries are >> crunching through a lot of data, and do things like using case statements >> within the sql to modify the values as it goes. >> >> Now, we're definitely not running these queries multiple times, but we >> are >> running multiple queries with each one based on the output of the >> previous >> one. So, the first one is a pass-thru query that returns a large dataset >> which then gets munched on by all the subsequent queries. Might that >> large >> data transfer cause some of the slowdown that we're seeing? Could it be >> faster to do all the processing within a sproc and only return the final >> dataset? >> >> I haven't worked with SQL Server in quite a while and I think I've >> probably >> forgotten a lot of what I learned about optimization, so I'll definitely >> need >> to brush up on that before attempting to create an optimized sproc to >> handle >> one of their reports. Guess there's no real way around that though :( >> >> "Sylvain Lafontaine" wrote: >> >>> Once compiled, there is no real difference between them if the >>> underlying >>> code is the same. You will have a difference if you are sending the >>> same >>> query a few hundred or a thousand times per second but for a job which >>> is >>> taking 10 minutes, I don't see what difference this could make. >>> >>> However, the best way to be sure would be to make the test; so why don't >>> you >>> grab one of these build sql string, put it in a stored procedure and see >>> what time this SP take to run? >>> >>> This doesn't mean that you cannot optimize your queries (if it is >>> possible >>> to do so, of course). However, you don't provide any information on >>> this >>> regard. For example, does the fact that some of these tables have some >>> hundred millions of rows means that all these rows must be read for >>> generating a report or if only a few rows are required? >>> >>> You can always hire someone but in my opinion, the first thing to do >>> would >>> be to buy some good books on optimizing SQL-Server and queries and the >>> second thing to do would be to take a serious look at the generated >>> query's >>> plans. >>> >>> -- >>> Sylvain Lafontaine, ing. >>> MVP - Technologies Virtual-PC >>> E-mail: http://cerbermail.com/?QugbLEWINF >>> >>> >>> "Bagger" <Bagger[ at ]discussions.microsoft.com> wrote in message >>> news:7C1EF3D5-DEE2-4A1B-93FB-FD0D184DA9D6[ at ]microsoft.com... >>> > I'm working with someone trying to revamp their reporting system. >>> > They >>> > are >>> > currently using SQL Server 2000 as the backend and Access for doing >>> > their >>> > reporting. The problem is that they are building these huge >>> > complicated >>> > pass-thru queries, and often stringing several queries together to get >>> > the >>> > dataset they need for their report. The analysts know Access pretty >>> > well >>> > and >>> > are comfortable with it, but the performance is just pretty bad. A >>> > few of >>> > the reports take more than 10 minutes to run. >>> > >>> > Now they aren't dealing with a small database. A couple of the tables >>> > have >>> > nearly a hundred million records in them. Now, I can't ask for anyone >>> > to >>> > tell me whether a specific set of queries, re-written as a stored >>> > procedure >>> > would be a major improvement or not, since I would have to give you a >>> > boatload of info on the tables, their relationships, and all the index >>> > info, >>> > among other things before you could really give a good answer. >>> > >>> > Since that's not very practical, what I'd like to find is more general >>> > information about pass-thru queries vs. stored procedures. What >>> > benefits >>> > can >>> > be gained from using one vs. the other. What is the recommended >>> > method of >>> > using Access as a reporting tool for SQL Server? What I'm trying to >>> > do >>> > here >>> > is to see if there is justification for continuing to use Access or >>> > not, >>> > and >>> > whether they continue to use it, or something else, whether stored >>> > procedures >>> > will be necessary to achieve the performance they want. If it looks >>> > like >>> > stored procedures are necessary, I'll need to make the case for them >>> > to >>> > hire >>> > someone to create those procedures. So this is about finding the info >>> > necessary to build a case for that, or for something else if the >>> > findings >>> > lead elsewhere. >>> >>> >>> > >