I have a query in MS SQL Server Management Studio that runs in about 40 Seconds. When I use that query to create a view and then query the view it takes as of now 25 plus minutes to return the data?? any thoughts.
I have a query in MS SQL Server Management Studio that runs in about 40 Seconds. When I use that query to create a view and then query the view it takes as of now 25 plus minutes to return the data?? any thoughts.
lack of indexing, server busy doing other processes?
not clear if you are querying sql server from access
views have their uses (usually around simple joins) but if you are using it to produce a heavily calculated view to which your subsequent query is looking for a subset, probably better to use a stored procedure.
you could try a pass thru query.
Performance tuning isn't a strength of mine, but I'll throw out the alternative of a table-valued function. I use them because I can pass them parameters like I can a stored procedure (and you can't pass them to a view), but I can also use one in a query:
SELECT Blah FROM FunctionName(Parameters)
I can't say it will solve your problem, but it might be worth a try.
Oh, and I deleted your duplicate thread in the Sharepoint forum.
It's not a table valued function. It's a query that I'm using to create a view in SQL Server.Performance tuning isn't a strength of mine, but I'll throw out the alternative of a table-valued function. I use them because I can pass them parameters like I can a stored procedure (and you can't pass them to a view), but I can also use one in a query:
SELECT Blah FROM FunctionName(Parameters)
I can't say it will solve your problem, but it might be worth a try.
I know it's not, that's why I offered the TVF as an alternative. You could create a TVF with the query, add parameters if you want, and see if it's any faster than querying the view.
Have you tried looking at the query execution plan in SSMS?
Does your view have a primary key field?
Out of interest if you have it working reasonably fast as a query why are you creating a view and querying that anyway?
The query is the first step for generating the data for the report. This query gets the Raw Data. A second query does some clean up and then a report summarizes and gives them the data they want. If I put the first query into Access It returns too much data and errors. I need to query on the server to reduce network traffic and speed things up. Because the final report going to go out daily and I'm trying to automate it. Seemed the best way was to create a view I can query for the report.
sounds more and more like a stored procedure is the way to go - pass the parameters to the stored procedure and have the final recordset returned which you can assign to the report. might be something like this
function getsqldata(SPName as string, optional params() as variant) as variant
'code to execute stored procedure and assign resulting recordset to function
end function
and in the report load event
set me.recordset=getsqldata("SPReport", date1, date2)
In SQL server procedures are no faster than views using the same SQL. Be carefull, order by in a view only make sense with a TOP instruction, otherwise they only slow down the view. As a SQL DBA I would first check my indexes and index fragmentation. There are some buildin reports that show the index usage and query performance. Create a view from the query, making sure the indexes are used. You can use SQL Server profiler to analyse the query.
Don't forget to set the proper security for the view.
Only if with:you mean this query updates, inserts or deletes data, you need a procedure. Otherwise T-SQL is in most cases powerfull enough to the job in one query.A second query does some clean up
If you already use SQL Server, why don't you use SQL Reporting to create your reports? You can puplish your reports on the intranet and print them from any location that has access to the website and the report. You can put security on a report level deciding who can view which report, and you can download the reports in pdf, Excel, XML and a few other formats.
I'm agree with the others comments, 40 seconds is quite along time for a query to run directly on a SQL server unless there are millions of records involved.
25 minutes for a view makes no sense if it is the same query. Use the execution plan on the query - it will highlight any missing indexes, and give you some hints as to what's slowing things down.
Don't do any sorting at this point.
And as others have said a stored procedure or TVF could use parameters to limit the number of records directly, further reducing the records returned and ultimately the processing time.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Can you post the T-SQL of the query? Then we could have a first check on slowing constructions like subqueries or filtering on calculated expressions.