asked on

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.

Peter Kiprop

Hi chtullu135,

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

Regards,
Pthepebble.

Juan Velasquez

ASKER

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

Jeffrey Coachman

<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...

Jeffrey Coachman

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

ASKER CERTIFIED SOLUTION

Juan Velasquez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Juan Velasquez

ASKER

I found the problem by myself