Referencing Access combo box from SQL View 1

[scliffe]

Apologies if this is answered elsewhere - I couldn't find it.

Basically, I have a view based on 2 other views. The 2 initial views running pretty quickly but as soon as they are combined, the time it takes increases greatly to about 35 seconds. In my limited experience I could then filter the result down on the Access end with the value in the combo box. Is there a way I can filter one of the sub views by referencing the combo box in some way and entering that value as the "dynamic" criteria? I'm aware that I could look down the stored procedure route but I have no experience of it and will happily go that route if someone can point me to a quick guide to how to create one. Are there any alternatives? Am I right in thinking that stored procedures aren't always the best option?

If I can't find a solution I'll have to convert the views back into Access and filter there and accept the smaller performance hit.

Thanks in advance

Simon

 

[philhege]

Simon, have you tuned the indices on these views?

 

[mrdenny]

There is no way to pass parameters to a view. They simply do not accept them. You need to check and correct the indexes on the base tables or indexes.

Using a stored procedure would probably make this easier to do overall, as well as remove the need for the views. (In my experience views are usually not actually needed.)

Am I right in thinking that stored procedures aren't always the best option?

Um, no. Stored procedures are pretty much always the correct route to go. They allow easy code modification without needed to update the client, they greatly enhance security within the database, and they allow SQL to make better use of cached execution plans.

The syntax to create a procedure is:

CREATE PROCEDURE usp_MyProcedureName
    @InputVariable int,
    @AnotherInputVariable varchar(25)
AS
/*Your SQL Code goes here*/
GO

That's all there is to creating a procedure. Look in Books OnLine for information about Stored Procedures. There is tons of information in there about stored procs.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[scliffe]

Only just returning to this code - I've gone down the stored procedure route. Now all I need to do is work out a way (if there is one!) to link the stored procedure back into the database. I know I can reference it in the code but I'm assuming that to write the filtered data back to the stored procedure I need to link it in... As I mentioned earlier, I'm new to SPs.

Create Procedure dbo.TempStudID(@ID Core_AdmissionsNumber)
AS
SELECT AdmissionsNumber
FROM dbo.tbl_student_name
WHERE (AdmissionsNumber = @ID)
GO

What I now need to do is pass across the Admissions Numbers as filtered by a combo box on a form (For 1 Admissions Number and also for a group of them). I don't have the time to convert it to an adp though that will be the eventual goal in the long term.

 

[ESquared]

See the FAQs in this forum, "Passing a list of values to a stored procedure" parts 2 and 3.

 

[scliffe]

Thinking I must be in stupid mode, I was no clearer after reading the FAQs, even after leaving it a while and coming back to it. The problem I still have is getting a table filtered from the combo box on an Access form in a database opened by multiple people on another server - eventually I'll create an adp.

I've looked at info regarding stored procedures but not found anything documenting what I'm looking for. Should I be looking at something else? I don't mind doing the reading/research but I'm obviously looking in the wrong places or for the wrong thing!

 

[scliffe]

I should have posted the view I'm trying to optimize. I've kept it as simple as I can. Basically, I want to filter to as few AdmissionNo rows as I can as this will ensure the views/reports based on this one cause as little work as possible.

SELECT dbo.StudentDets.AdmissionNo, dbo.AttendanceDets.AttDateTime, dbo.AttendanceDets.AttMarkID, dbo.AttendanceDets.ClassID,
dbo.AttendanceDets.MinutesLate, dbo.AttendanceDets.PeriodID, dbo.AttendanceDets.StudentID, dbo.AttendanceDets.Process
FROM dbo.AttendanceDets INNER JOIN
dbo.StudentDets ON dbo.AttendanceDets.StudentID = dbo.StudentDets.StudentID