Slow response on Form in Access with queries converted SQL views

[RoadrunnerII]

Hi
Great site
I have moved on from my previous thread and converted all the Access Queries to SQL views
I have an Access form that has an Row Source with an enbedded Select statement with a couple of trims and Like variable in it.
What the form is doing is a Find within the database on either Serial number workstation number or Model number.
I have converted the original query to a SQL view but still using Access for the Forms and Reports
It is only really slow on a slow link ie VPN connection or WAN connection.
This is the only Form that is acting up. The rest work great in the views and are much faster.
The Sql view is about 3 seconds to complete on 8000 records in SQL Management Studio so it seems fine but it does not have the actual LIKE Variable within the view as I am not sure how I would transfer the variable to the view from Access

Need some help here as I am stumped

RRII

 

[RoadrunnerII]

Access Form Slow

I have talked with our DBA and converted the actual code in the Form into a SQL Server Stored Procedure
The sp works fine but trying to get it to take input from the form is not working
I have linked the SP to a Passthrough Query
My issue is how do I get the Form to pass the variable txtSearchExpression to the Sp

Here is the original sql the form was running
SELECT DISTINCT qrySerializedInventoryActive.[SerialNumber], LTRIM(RTRIM([SerialNumber] & [ModelName] & [WorkstationNumber])) AS 'Name', qrySerializedInventoryActive.[ModelName], qrySerializedInventoryActive.[WorkstationNumber] FROM qrySerializedInventoryActive WHERE (((LTRIM(RTRIM([SerialNumber] & " " & [ModelName] & " " & [WorkstationNumber]))) Like [txtSearchExpression]));

Anyone have an idea what I can do?
Thanks
RRII

 

[SQL_Hell]

Hi,

Could you answer the following questions for me (the solution will differ depending on the answers)

Do you have .mdb or .adp?

How is this Stored Proc being used? i.e. does the user click a button to open a new form with the sp as its record source and then you want to reference text box on the previous form? please explain the process

 

[RoadrunnerII]

Slow Form

K
Keep in mind I am pretty new to the world of access and SQL server
I had assistance to a point from an Access Developer of sorts but that assistance is no longer available
Some of the code was found off of forums and tweaked to work for our application

It is an Access mdb

In the Original we have a main form with a find inventory button
The Find Inventory button uses a findSerial Form
The findSerial Form has 3 unbound text boxes

The First unbound text box sets a default value of '*' and has the name txtSearchExpression (It is a hidden txt box as well)
This allows the third text box to be populated with all data when you open the find

The Second unbound text box has an ON Change Event Procedure within it to do the search when you start typing in the criteria it searches on each character across multiply columns in the source table
Here is the Code for it
Private Sub txtEntry_Change()
On Error GoTo Err_txtEntry_Change
' So simple it's almost funny. This builds the search pattern string as characters
' are added or removed from the entered text. Concactenate an asterisk on the
' end of the entered text and requery the listbox source. Look at the listbox
' source to see how it is populated. The working version had a recordsource
' of nearly 5,000 records. It was still pretty fast, even on a 486.
Me!txtSearchExpression = UCase("*" & Me!txtEntry.Text & "*")
lstFound.Requery
Exit_txtEntry_Change:
Exit Sub
Err_txtEntry_Change:
MsgBox Err.Description
Resume Exit_txtEntry_Change
End Sub


(I am thinking the above piece of code is causing much of the latency because it is searching on each character as they are typed!)

The Third unbound text box has 3 event Procedures to cover off Enter Key or Cursor Key movements or Mouse double clicks on the results of the text search
When you doubleclick or enter on the results of the txt search it closes the find window and updates the main Source form with the record information for the item found

The record source of the form is an Access query that has been converted to a sql view
Also within the Form we have a Table/Query Row Source SQL code
(This is the part we converted to a Stored Procedure but I have not found a way to incorporate it into the form as yet.)

Here is the Source code we converted to a SP

SELECT DISTINCT qrySerializedInventoryActive.[SerialNumber], LTRIM(RTRIM([SerialNumber] & [ModelName] & [WorkstationNumber])) AS 'Name', qrySerializedInventoryActive.[ModelName], qrySerializedInventoryActive.[WorkstationNumber] FROM qrySerializedInventoryActive WHERE (((LTRIM(RTRIM([SerialNumber] & " " & [ModelName] & " " & [WorkstationNumber]))) Like [txtSearchExpression]));

Any suggestions on how to make this work are much appreciated

RRII

 

[SQL_Hell]

Here is an example of how to envoke a stored procedure with parameters in VBA code, this should get you pointing in the right direction.

Private Sub Command0_Click()

Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim Rs1 As ADODB.Recordset
Dim Connect As String



Connect = "Provider=SQLOLEDB;" & _
        "Data Source=[COLOR="Red"]hdx90sql21[/COLOR];" & _
        "Initial Catalog=master;" & _
        "Integrated Security=SSPI"

' Establish connection.
Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = Connect
Conn1.Open

' Open recordset.
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "sp_help"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(1).Value = "sys.sysobjects"
Set Rs1 = Cmd1.Execute()


Me.Text1 = Rs1.Fields("name").Value
Me.Text2 = Rs1.Fields("owner").Value
End Sub

Create a simple form with a command button to fire the above code and some text boxes to recieve the output, then try and use this approach in your current solution
The code in red will need to be changed to your server name

 

[RoadrunnerII]

Thanks for the reply
Just to make sure I did this right
I create the command button and two text boxes
Text 1 and Text 2
Plug the code into the VB for the command button
Change to my server name

So when I click on the command button if I did everything correct the output should be in the txt boxes
Text1 = sysobjects
Text2 = sys

Right?
Sorry I am not real versed on VB code so learning my way through it slowly
Thanks
RRII

 

[SQL_Hell]

Yes that's correct

No worries we all have to start somewhere and what I have posted is the more complicated (but better) way of executing a stored proc.

So moving forward... what you have done here is make use of something called the ado command object, the ado command object is used in most programming languages such as asp.net / c# / VB and the syntax is almost identical to how it is in VBA.

So now we want to get the parameter value from the form as opposed to hard coding it in the example. Create another text box called text3 and type the value sys.sysobjects, here is the new code (changes in green)

Private Sub Command0_Click()

Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim Rs1 As ADODB.Recordset
Dim Connect As String
[COLOR="Lime"]Dim Name as string 'variable for holding form value

Name = me.text3[/COLOR]

Connect = "Provider=SQLOLEDB;" & _
        "Data Source=hdx90sql21;" & _
        "Initial Catalog=master;" & _
        "Integrated Security=SSPI"

' Establish connection.
Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = Connect
Conn1.Open

' Open recordset.
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "sp_help"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(1).Value = [COLOR="lime"]Name[/COLOR]
Set Rs1 = Cmd1.Execute()


Me.Text1 = Rs1.Fields("name").Value
Me.Text2 = Rs1.Fields("owner").Value
End Sub

 

[RoadrunnerII]

K
I added the text box text3 (Made sure it was called Text3)
When I run the VB Itget a run-Time Error'94' Invalid use of Null
If I do the debug it highlights Name = Me.Text3
Hmmm...
RRII

 

[RoadrunnerII]

K
I went back and read your post again
I did not type the sys.sysobjects in the text3 field
Now it makes sense
Sorta

RRII

 

[RoadrunnerII]

So moving forward
I already have a form with the 3 txt boxes in my application
So I should be able to put in similar VB code on the one box and make it work right?
I need to reference the correct DB
Then I need to point it at the Stored Proc or am I getting ahead of myself here
RRII

 

[RoadrunnerII]

So I used the example and I can get results from the Stored Procedure
Great now I can get results from the Stored Procedure
But
With the original request we had "*" so the one result window showed all the items to start with
IE we had all 8209 records listed Not sure this is a good idea
What is more important is we could search for parts of the search text
As in
Me!txtSearchExpression = UCase("*" & Me!txtEntry.Text & "*")
lstFound.Requery

Will that syntax still work?
RRII

 

[tokoloshi]

Also, if you are going to use LIKE and you need to do fuzzy searches (%something%) on a given column, create an index on that column. Particularly, configure your database for full text indexing and then create a full text index on that column. You can then use the CONTAINS predicate and your performance - even across your VPN - should be a lot faster.

 

[RoadrunnerII]

Also, if you are going to use LIKE and you need to do fuzzy searches (%something%) on a given column, create an index on that column. Particularly, configure your database for full text indexing and then create a full text index on that column. You can then use the CONTAINS predicate and your performance - even across your VPN - should be a lot faster.

Just talked with my DBA and he will be putting in the indexing and changing the Stored Procedure to use CONTAINS
Thanks for the suggestion

 

[RoadrunnerII]

I am trying to merge the code from the original form into the updated code from SQL_HELL
It does not like 1stFound.Requery
What am I missing???

Private Sub txtEntry_Change()
On Error GoTo Err_txtEntry_Change
Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim Rs1 As ADODB.Recordset
Dim Connect As String
Dim txtSearchExpression As String 'variable for holding form value

Me!txtSearchExpression = UCase("*" & Me!txtEntry.Text & "*")
lstFound.Requery
Connect = "Provider=SQLOLEDB;" & _
"Data Source=S1161544;" & _
"Initial Catalog=CDN_Logistics;" & _
"Integrated Security=SSPI"
' Establish connection.
Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = Connect
Conn1.Open
' Open recordset.
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "dbo.Find_Product"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(1).Value = txtSearchExpression
Set Rs1 = Cmd1.Execute()

Me.Text39 = Rs1.Fields("SerialNumber").Value
Me.Text41 = Rs1.Fields("ModelName").Value
Me.Text43 = Rs1.Fields("WorkstationNumber").Value

Exit_txtEntry_Change:
Exit Sub
Err_txtEntry_Change:
MsgBox Err.Description
Resume Exit_txtEntry_Change
End Sub

 

[RoadrunnerII]

Well
I figured out the 1stFound.Requiry
It was the name of one of my unbound text boxes
Next question
I need to be able to plug in the value but with wildcards on each side of the data entered
From Sql_Hell code that would be on each side of Text3 as in we are searching for part of a Serial Number or Part of a Model Number or Part of a Workstation ID# and list all entries that contain the entered text in a drop down box
We then click on the appropriate entry and it updates the primary form with the details for that item.
What would be the best way to accomplish this?

 

[SQL_Hell]

Hi again,

Yep remove that 1stFound.Requiry, its not needed anymore. I would remove the method of requerying after one character is typed all together it creates too much overhead in my opinion and as you get more data your tables constantly requerying is going to start taking some significant load on the server.

For the wild cards you can either add them to your variables in VBA or add them to your stored proc, it makes no difference but given the choice I would add it to the stored proc, something like:

CREATE PROCEDURE wild @name as varchar(10)
AS

SELECT *
FROM sys.sysobjects
WHERE name LIKE @name + '%'
-- WHERE name LIKE '%' + @name + '%' --ANOTHER EXAMPLE

 

[RoadrunnerII]

K
Again thank you for the help very much appreciated
I checked the SP and it is using the suggested syntax
Still having issues at the Access Form to get it to find more than a single instance of the variable
As in if I search for a '1' It should come up with all the serial numbers workstation#s and Model's that contain the #1 in those fields
I am missing something

RRII

Hi again,

Yep remove that 1stFound.Requiry, its not needed anymore. I would remove the method of requerying after one character is typed all together it creates too much overhead in my opinion and as you get more data your tables constantly requerying is going to start taking some significant load on the server.

For the wild cards you can either add them to your variables in VBA or add them to your stored proc, it makes no difference but given the choice I would add it to the stored proc, something like:

CREATE PROCEDURE wild @name as varchar(10)
AS
 
SELECT *
FROM sys.sysobjects
WHERE name LIKE @name + '%'
-- WHERE name LIKE '%' + @name + '%' --ANOTHER EXAMPLE

 

[SQL_Hell]

Ah ok...hmm

So you want it to search more than one field in the database for the occurance of what ever has been typed in the text box? right?

I am going to have to write you a stored proc
Please post the code for the existing stored procedure and the DDL (table design) for the table being searched (your DBA should be able to do this easily with the scripting tools in SSMS)

Also please specify every field that need to be searched.

 

[RoadrunnerII]

SQL_Hell
Here are the pieces you asked for
Thanks again
RRII


Fields to search are SerialNumber, ModelName and WorkstationNumber

Here is the current SP
USE [CDN_Logistics]
GO
/****** Object: StoredProcedure [dbo].[Find_Product] Script Date: 08/14/2008 10:40:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Find_Product] @searchstring varchar(20)
AS
select @searchstring = '%' + @searchstring + '%';
SELECT DISTINCT SerialNumber,
LTrim(Rtrim([SerialNumber] +' '+ [ModelName] +' '+ [WorkstationNumber])) AS [Name],
ModelName, WorkstationNumber
FROM SerializedInventory
WHERE EndofLife=0 AND
[group] = (SELECT defswitchboard FROM tblDefaults) AND
[SerialNumber] + [ModelName] + [WorkstationNumber] Like @searchstring;


Here is the DDL

USE [CDN_Logistics]
GO
/****** Object: Table [dbo].[SerializedInventory] Script Date: 08/14/2008 10:49:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SerializedInventory](
[SerialNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('Serial Number?'),
[InventoryStatus] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UsageStatus] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Manufacturer] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ModelName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OutstandingSwapReturn] [bit] NULL DEFAULT ((0)),
[TrackingReference#] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TrackingReferenceName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TrackingReturnS/N] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TrackingDate] [datetime] NULL,
[OnLoan] [bit] NULL DEFAULT ((0)),
[Approver] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClientName] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LoanReference#] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LoanNotes] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LoanedOutDate] [datetime] NULL,
[ReturnedDate] [datetime] NULL,
[PinNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MACAddress] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PONumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReceiveLog] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PDAPhoneNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WorkstationNumber] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PDAServiceProvider] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EndofLife] [bit] NULL DEFAULT ((0)),
[PhysicalLocation] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Group] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InventoryType] [int] NULL DEFAULT ((0)),
[FieldSpare1] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT ('spare'),
[FieldSpare2] [int] NULL DEFAULT ((0)),
[FieldSpare3] [datetime] NULL DEFAULT ((0)),
[FieldSpare4] [bit] NULL DEFAULT ((0)),
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [SerializedInventory$PrimaryKey] PRIMARY KEY CLUSTERED
(
[SerialNumber] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Statistic [SerailizedInventory$SerialNumber_Group_EndofLife] Script Date: 08/14/2008 10:49:17 ******/
CREATE STATISTICS [SerailizedInventory$SerialNumber_Group_EndofLife] ON [dbo].[SerializedInventory]([SerialNumber], [Group], [EndofLife])
GO
/****** Object: Statistic [SerializedInventory$EndofLife_Group] Script Date: 08/14/2008 10:49:17 ******/
CREATE STATISTICS [SerializedInventory$EndofLife_Group] ON [dbo].[SerializedInventory]([EndofLife], [Group])
GO
USE [CDN_Logistics]
GO
ALTER TABLE [dbo].[SerializedInventory] WITH NOCHECK ADD CONSTRAINT [SerializedInventory$Inventory TypeSerialized Inventory] FOREIGN KEY([InventoryType])
REFERENCES [dbo].[InventoryType] ([TypeID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SerializedInventory] CHECK CONSTRAINT [SerializedInventory$Inventory TypeSerialized Inventory]
GO
ALTER TABLE [dbo].[SerializedInventory] WITH NOCHECK ADD CONSTRAINT [SerializedInventory$ManufacturerNamesSerializedInventory] FOREIGN KEY([Manufacturer])
REFERENCES [dbo].[ManufacturerNames] ([OEMName])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SerializedInventory] CHECK CONSTRAINT [SerializedInventory$ManufacturerNamesSerializedInventory]
GO
ALTER TABLE [dbo].[SerializedInventory] WITH NOCHECK ADD CONSTRAINT [SerializedInventory$Model NamesSerialized Inventory] FOREIGN KEY([ModelName])
REFERENCES [dbo].[ModelNames] ([Model])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SerializedInventory] CHECK CONSTRAINT [SerializedInventory$Model NamesSerialized Inventory]
GO
ALTER TABLE [dbo].[SerializedInventory] WITH NOCHECK ADD CONSTRAINT [SerializedInventory$Physical LocationSerialized Inventory] FOREIGN KEY([PhysicalLocation])
REFERENCES [dbo].[PhysicalLocation] ([SITE_NAME])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SerializedInventory] CHECK CONSTRAINT [SerializedInventory$Physical LocationSerialized Inventory]
GO
ALTER TABLE [dbo].[SerializedInventory] WITH NOCHECK ADD CONSTRAINT [SerializedInventory$Service ProviderSerialized Inventory] FOREIGN KEY([PDAServiceProvider])
REFERENCES [dbo].[ServiceProvider] ([TelephoneServiceProvider])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SerializedInventory] CHECK CONSTRAINT [SerializedInventory$Service ProviderSerialized Inventory]
GO
ALTER TABLE [dbo].[SerializedInventory] WITH NOCHECK ADD CONSTRAINT [SerializedInventory$Tbl_GroupsSerialized Inventory] FOREIGN KEY([Group])
REFERENCES [dbo].[Tbl_Groups] ([Group])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SerializedInventory] CHECK CONSTRAINT [SerializedInventory$Tbl_GroupsSerialized Inventory]
GO
ALTER TABLE [dbo].[SerializedInventory] WITH NOCHECK ADD CONSTRAINT [SerializedInventory$tlkpInventoryStatusSerialized Inventory] FOREIGN KEY([InventoryStatus])
REFERENCES [dbo].[tlkpInventoryStatus] ([InvStatus])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SerializedInventory] CHECK CONSTRAINT [SerializedInventory$tlkpInventoryStatusSerialized Inventory]
GO
ALTER TABLE [dbo].[SerializedInventory] WITH NOCHECK ADD CONSTRAINT [SerializedInventory$tlkpUsageStatusSerialized Inventory] FOREIGN KEY([UsageStatus])
REFERENCES [dbo].[tlkpUsageStatus] ([UsageStatus])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SerializedInventory] CHECK CONSTRAINT [SerializedInventory$tlkpUsageStatusSerialized Inventory]
GO
ALTER TABLE [dbo].[SerializedInventory] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$SerializedInventory$SerialNumber$disallow_zero_length] CHECK ((len([SerialNumber])>(0)))
GO
ALTER TABLE [dbo].[SerializedInventory] CHECK CONSTRAINT [SSMA_CC$SerializedInventory$SerialNumber$disallow_zero_length]

Ah ok...hmm

So you want it to search more than one field in the database for the occurance of what ever has been typed in the text box? right?

I am going to have to write you a stored proc
Please post the code for the existing stored procedure and the DDL (table design) for the table being searched (your DBA should be able to do this easily with the scripting tools in SSMS)

Also please specify every field that need to be searched.

 

[SQL_Hell]

Ok well that stored proc should work fine so no problems there, I would have done the stored proc in a different way but the end result would be the same.

does it still only return 1 row in SSMS?