How to speed up a MS SQL Pivot view

Hello,
I have an Access / SQL server application which uses a MS Sql view populate an Access front-end form at runtime.  Although the view executes quickly in SSMS, it takes a long time to populate the form.  I'm trying to think of alternative ways to populate that form using the data displayed by the view.  I'm thinking of using a table to hold the pivot view data and making that table as the data source for the unbound form.  This is a multiuser environment and I need to figure out a way to load the form faster.  I'm looking for advice on the best approach to take.  The record set is about 20,000 records.  I'd appreciate any ideas.
chtullu135Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chtullu135Connect With a Mentor Author Commented:
I found what the bottleneck was.  Basically I redid all the underlying views so that I only retrieved the data I needed.  I also was making multiple calls to the backend in order to populated multiple and similar comboboxes.  I simplified that stored procedure and used it to populate all the similar comboboxes.  Now the form loads in about 90 seconds, which is much better than the 10 minutes it was taking
0
 
Peter KipropCommented:
Hi chtullu135,

which control do you have on the form? How are you populating currently using a loop or what?

Regards,
Pthepebble.
0
 
chtullu135Author Commented:
I am populating the form via a view in the following manner

Private Sub Form_Load()
Const SUBNAME = "Form_Load"
'On Error GoTo Err_Handler

    ClearComboBoxes
    'PopulateMainComboBoxes
    'LoadRetrieve
   
    gstrFilePath = ""
    strWhereClause = ""
    strRecordSource = "SELECT dbo_vwPivotedReviewsRevised.USI, dbo_vwPivotedReviewsRevised.WorkStream, dbo_vwPivotedReviewsRevised.GFP, " & _
                       "dbo_vwPivotedReviewsRevised.Review1 , dbo_vwPivotedReviewsRevised.Status1, " & _
                       "dbo_vwPivotedReviewsRevised.Review2 , dbo_vwPivotedReviewsRevised.Status2, " & _
                       "dbo_vwPivotedReviewsRevised.Review3 , dbo_vwPivotedReviewsRevised.Status3, " & _
                       "dbo_vwPivotedReviewsRevised.Review4 , dbo_vwPivotedReviewsRevised.Status4, " & _
                       "dbo_vwPivotedReviewsRevised.Review5 , dbo_vwPivotedReviewsRevised.Status5, " & _
                       "dbo_vwPivotedReviewsRevised.Review6 , dbo_vwPivotedReviewsRevised.Status6, " & _
                       "dbo_vwPivotedReviewsRevised.Review7 , dbo_vwPivotedReviewsRevised.Status7, " & _
                       "dbo_vwPivotedReviewsRevised.Review8 , dbo_vwPivotedReviewsRevised.Status8 " & _
                       "FROM dbo_vwPivotedReviewsRevised "
                       
    strClearFilterSource = strRecordSource
                       
    Me.RecordSource = strRecordSource
    'Me.cmbFilterByGFP.RowSource = "SELECT DISTINCT dbo_vwPivotedReviewsRevised.GFP FROM dbo_vwPivotedReviewsRevised ORDER BY dbo_vwPivotedReviewsRevised.GFP"


'Exit_Sub:
'    Exit Sub
'
'Err_Handler:
'    MsgBox "Module Name: " & MODULENAME & vbCrLf & _
'            "Procedure: " & SUBNAME & vbCrLf & _
'            "Error Number: " & Err.Number & vbTab & "Error Description: " & Err.Description
'    Resume Exit_Sub
   
End Sub
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Jeffrey CoachmanMIS LiasonCommented:
<it takes a long time to populate the form.>
Please define this in Chronological terms please...
"A Long Time" is a very vague term...

Note that the time this takes, just may be "what it is"
1. You are pulling 20,000 records over the wire, so the network may be a limiting factor here...

2. You are creating a Pivot. Pivots in general take time to create as they must aggregate the data.
The more records, the more complex the aggregation, the more fields you are using, ...the "slower" the performance.

3. Also you have a "mystery" call in there: ClearComboBoxes
...it is not clear what this is...

4. You are not declaring your variables. (strWhereClause, strRecordSource, strClearFilterSource )
This forces them all to me Variants which takes longer to process...
0
 
Jeffrey CoachmanMIS LiasonCommented:
RichardRost,

Just a note...
EE does not really allow a Link to a/your site in your signature
This info can be in your profile though...

;-)

jeffCoachman
0
 
chtullu135Author Commented:
I found the problem by myself
0
All Courses

From novice to tech pro — start learning today.