Access FE/Azure SQL BE, Simple query slow (1 Viewer)

[kloklo]

I am doing my first baby steps with SQL Azure backend and Access 2016 as front end.

I have used SSMA to migrate two tables, for a start.
tblInvoices (140 000 rows, 18 columns) and
tblInvoiceDetails (320 000 rows, 24 columns)
I use SQL Server Native Client 11.0 ODBC Driver for connection

All is well when I'm in the read-only area. I was thrilled to learn how to pass parameters to a stored procedure through pass through query and do some updates that way.
I was very happy when created my first view with some calculations and based Invoices list with invoice totals on it.

But....I am so frustrated because of one basic thing. I can't make my Invoice form load fast no matter what I do. To figure out what is slowing it down I have stripped it to the bare bones, no comboboxes, no subform with invoice details... nothing but fields from qryInvoices based on linked table tblInvocies.
It's not just the form, the qryInvoices which is the recordsource itself is slow.

Example...I want to click on invoice in the list and open details form for that one invoice.
If I run this on SSMS it is executed in a blink of an eye:
SELECT InvoiceID,InvoiceDate,InvoiceSalesSlip, etc....
FROM tblInvoices
WHERE InvoiceSalesSlip=15

If I create the same query on a linked table in Access and put this in WHERE clause:
[forms]![frmInvoicesList].[InvoiceSalesSlip]
I have to wait 3 seconds no matter the number of columns I choose (just one or all 18 of them).
InvoiceSalesSlip is Int, Primary key and clustered index was created for it during the migration.

I thought that reference to a Invoices List form was the problem but even if I hardcode the condition to a number, say 15, it still needs 3 seconds to run.
If I try to filter by InvoiceID which has non-clustered index on it, again 3 seconds.

If I remove the where clause from qryInvoices and make my choice in VBA:
DoCmd.OpenForm "frmInvoices", acNormal, , "[InvoiceSalesSlip]= " & Me.InvoiceSalesSlip
...again 3 seconds for the data to populate the form.


What am I doing wrong? Or should I be happy with 3 seconds?
I really need an updateable record source for my Invoice form so it has to be either linked to a table or a view. Right?
There is no use of a view in this case cause I can't feed any parameters to it.
Taking the unbound forms road is not appealing. Access is great precisely because of bound forms....
So, I'm lost...

Thanks a million in advance for any advice

 

[Simon_MT]

You could try:

SqlServer: create a view to emulate the Access Query. Link the view rather that the individual tables. Index InvoiceSalesSlip with SQL server.

Simon

 

[kloklo]

Thanks for the reply, Simon, but, as far as I know views can't take parameters.
I even tried the silly thing to create a view out of whole tblInvoices and query that view in Access, feeding it the parameter for InvoiceSalesSlip, but it was just as slow as querying the linked table itself.

I mean, 3 sec is not the end of the world but it is annoying.

I have attached a screenshot of columns and indexes. Do they look all right to you?
I'm still not totally sure what are non-clustered indexes good for and what are clustered good for. Oh, man, I have so much more to learn

 

[AnthonyGerrard]

YOu could use a sql function to return a table (select statement or whatver sql you want) to use as if its a parametrized view.

I am looking at access to azure myself - any updates you have on your progress would be most helpful.

 

[Rx_]

http://searchwpplugins.com/tag/microsoft-azure/
Not moving forward as fast as expected.
Building a Hybrid system - Access front end -> Azure back end <- WordPress
The Access front end (Back Office Manager) will have a rich graphical interface and connect to all tables / views in Azure. The WordPress will just connect to what is needed for the Webpage functionality.

This post could probably will find itself in the Access Web later.
I am exploring BeaverBuilder to create a WordPress page with live data from Azure.
The intent would be to build a Back-Office management application using full Access connected to Azure.
Then, using some of the views in Azure in the Wordpress web.
Listed are a couple of Azure APP for part of this.