|
|
You're in the
sql server mseq
group:Promt user for criteria ?
sql server mseq:
specific criteria like date range (between ? and ?) in a view. I have a query in a view but I need to prompt the user for a date range, my front end is access 2002 I can do it as a passthrough but it takes to long to run and while testing I found that as a view it runs in half the time. Thanks
[quoted text, click to view]
On Fri, 20 Jan 2006 07:36:05 -0800, Xavier wrote: >I know it can be done with a SP but is there a way to prompt a user for >specific criteria like date range (between ? and ?) in a view. >I have a query in a view but I need to prompt the user for a date range, my >front end is access 2002 I can do it as a passthrough but it takes to long to >run and while testing I found that as a view it runs in half the time. Hi Xavier, SQL Server can't ever prompt the user. Not in a view, and not in a stored procedure either. If you refer to prompting for arguments in the front end, then passing them as parameters to the back end: that is possible in stored procs, but not in a view. But you can use variables in a SELECT statement that queries a view. However, I am intrigued by your statement that you found a view to be faster than a stored procedure. While I don't doubt your observations, I'm pretty sure that there's no such blanket statement about performance of views vs stored procedures. I suspect that is has something to do with the specific details of your tables, your stored procedure and your view. To investigate this further, you'll have to give more details: how do your tables look (CREATE TABLE statements), how are the view and the stored procedure defined (CREATE VIEW / CREATE PROC), what does your data typically look like (INSERT statements for a small sample of your data) and how do you call the view and the stored proc? Chcek out www.aspfaq.com/5006 for some tips on how to assemble this information. --
Hugo
You are correct I mistakenly said faster than a SP but I should has said faster that a passthrough query from my front end which is access. I could not run my report from access unless I add the view to my access front ent as a linked table, is there any other way ? for an access report to run a sql view and pass a parameter, like a date range to extrack particular data example ()now minus 48hrs everytime the user runs it. Thanks Xavier [quoted text, click to view] "Hugo Kornelis" wrote:
> On Fri, 20 Jan 2006 07:36:05 -0800, Xavier wrote: > > >I know it can be done with a SP but is there a way to prompt a user for > >specific criteria like date range (between ? and ?) in a view. > >I have a query in a view but I need to prompt the user for a date range, my > >front end is access 2002 I can do it as a passthrough but it takes to long to > >run and while testing I found that as a view it runs in half the time. > > Hi Xavier, > > SQL Server can't ever prompt the user. Not in a view, and not in a > stored procedure either. > > If you refer to prompting for arguments in the front end, then passing > them as parameters to the back end: that is possible in stored procs, > but not in a view. But you can use variables in a SELECT statement that > queries a view. > > However, I am intrigued by your statement that you found a view to be > faster than a stored procedure. While I don't doubt your observations, > I'm pretty sure that there's no such blanket statement about performance > of views vs stored procedures. I suspect that is has something to do > with the specific details of your tables, your stored procedure and your > view. > > To investigate this further, you'll have to give more details: how do > your tables look (CREATE TABLE statements), how are the view and the > stored procedure defined (CREATE VIEW / CREATE PROC), what does your > data typically look like (INSERT statements for a small sample of your > data) and how do you call the view and the stored proc? > > Chcek out www.aspfaq.com/5006 for some tips on how to assemble this > information. > > -- > Hugo Kornelis, SQL Server MVP
[quoted text, click to view]
On Mon, 23 Jan 2006 06:36:03 -0800, Xavier wrote: >Hugo > >You are correct I mistakenly said faster than a SP but I should has said >faster that a passthrough query from my front end which is access. > >I could not run my report from access unless I add the view to my access >front ent as a linked table, is there any other way ? for an access report to >run a sql view and pass a parameter, like a date range to extrack particular >data example ()now >minus 48hrs everytime the user runs it. Hi Xavier, When using Access, you want to make sure that network traffic is kept at a minimum. Stored procedures and pass-through queries meet this requirement without any doubt. I'm less sure about queries on linked tables. I have read reports claiming that Access will fetch a complete linked table over the network to execute the query client-side. I have also read reports claiming that the former reports are rubbish. My personal experience with Access is too limited to be able to tell which reports are true and which aren't. If you want to find out, then run a profiler trace while running Access and check what Access really sends to the server. I would personally use a stored procedure, but that's probably because I'm more at ease on SQL Server - I know how to squeeze every drop of performance out of the SP code; I'm not nearrly as proficient in optimizing Jet SQL. If your tests indicate that an Access query on an Access linked table (to a SQL Server table or view) is faster than calling a stored procedure, then by all means go for it. --
Hugo
Thank you, based on your experience with SQL can you recommend and good books on SP and queries within sql. We are planning on moving from access as a front end reporting tool and replacing it with Crystal Reports, any thoughts on this you might want to share? We have a web based application where our users need to print certain forms with the data that is on their screen plus a little more not seen at the moment that we have determined this information will print on every form allways. The provider of our web based application which uses a propriatary tool kit together with FrontPage to compile the forms will only support Access or Crystal Reports. Thanks Xavier [quoted text, click to view] "Hugo Kornelis" wrote:
> On Mon, 23 Jan 2006 06:36:03 -0800, Xavier wrote: > > >Hugo > > > >You are correct I mistakenly said faster than a SP but I should has said > >faster that a passthrough query from my front end which is access. > > > >I could not run my report from access unless I add the view to my access > >front ent as a linked table, is there any other way ? for an access report to > >run a sql view and pass a parameter, like a date range to extrack particular > >data example ()now > >minus 48hrs everytime the user runs it. > > Hi Xavier, > > When using Access, you want to make sure that network traffic is kept at > a minimum. Stored procedures and pass-through queries meet this > requirement without any doubt. I'm less sure about queries on linked > tables. > > I have read reports claiming that Access will fetch a complete linked > table over the network to execute the query client-side. I have also > read reports claiming that the former reports are rubbish. My personal > experience with Access is too limited to be able to tell which reports > are true and which aren't. If you want to find out, then run a profiler > trace while running Access and check what Access really sends to the > server. > > I would personally use a stored procedure, but that's probably because > I'm more at ease on SQL Server - I know how to squeeze every drop of > performance out of the SP code; I'm not nearrly as proficient in > optimizing Jet SQL. > > If your tests indicate that an Access query on an Access linked table > (to a SQL Server table or view) is faster than calling a stored > procedure, then by all means go for it. > > -- > Hugo Kornelis, SQL Server MVP
[quoted text, click to view]
On Thu, 26 Jan 2006 18:31:01 -0800, Xavier wrote: >Hugo > >Thank you, based on your experience with SQL can you recommend >and good books on SP and queries within sql. Hi Xavier, I've learnt most from Books Online and by trial and error. But I'll give you some titles that are often recommended in these groups by very kowledgeable persons. Two books specifically targetted towards coding for SQL Server: * Advanced Transact-SQL for SQL Server 2000 (Ben-Gan/Moreau) * The Guru's Guide to Transact-SQL (Henderson) A book about the inner working of SQL Server - a great aid if you start to think about fine-tuning, since knowing how things work is the best way to tune: * Inside SQL Server 2000 (Delaney) An advanced book, full with tips and tricks. Definitely no easy stuff here. And it uses ANSI standard SQL - you'll have to change some things here and there to amke it run on SQL Server: * SQL for Smarties (Celko) [quoted text, click to view] > >We are planning on moving from access as a front end reporting tool and >replacing it with Crystal Reports, any thoughts on this you might want to >share? I've never worked with Crystal Reports. --
Don't see what you're looking for? Try a search.
|
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |