Extreme performance penalty using SQL Server connection

[WindForce]

Hi
Using SQL Server 2016. The ADO Connection is using Sql 2016 version.
I set a tabled-value function as data source for a list control. I have an argument ( :FromDate ) fed by a control in the ux, then the to use the table-valued function i use:

select * from ExpoDetails ( :FromDate )

Of course to make this work i have to de-select "use portable sql".

This works, but it takes 25 to 30 seconds to run ( for a 30K records return ) while running the same query in SSMS is almost instant, one second at most.

Tried selecting 2014,2017,2012 and 2008 in the sql version in the sql connection , with minor effects.

Is there some way to improve this?

Thanks
Jaime

[WindForce]

Re: Extreme performance penalty using SQL Server connection

I selected "paginate data" in the list control, took down the time to 5-6 seconds, which is livable :-)

[mikeallenbrown]

Re: Extreme performance penalty using SQL Server connection

How many columns are in ExpoDetails? Do you really need all of them? If not then select the individual columns you need. Also, do you really need all 30,000 records? If not filter it some more. You can also try using a viewbox. It's more lightweight than the list control.

[WindForce]

Re: Extreme performance penalty using SQL Server connection

Well in fact i need to export to Excel, after the list is loaded the user clicks export. In fact i could write code to export directly, but my users like to visualize the results prior to exporting. I generally do this with grids, but calling a table-valued function with an argument in a grid is a pita...
So, as stated above, i selected the "paginate" option in the grid, and after reducing the first load to 15 records, it loads in around 3 seconds, which is great. Clicking on the "excel export" button runs the query straight from SQL to excel, which indeed takes 1 to 2 seconds, so all is well.

[glenschild]

Re: Extreme performance penalty using SQL Server connection

In sql do you have an index on the field used to filter the records?

[Ted Giles]

Re: Extreme performance penalty using SQL Server connection

Index will speed it up.
Select from reads the whole Db which is not what you need.

[WindForce]

Re: Extreme performance penalty using SQL Server connection

It's not an sql issue, as stated, my query takes less than a second to run.
Without selecting pagination, the list control needed to load 30000 rows, that's why it took so long. I set pagination and selected 15 rows, it takes now 2 to 3 seconds

[Jonathan Freestone]

Re: Extreme performance penalty using SQL Server connection

try virtualization on the list, so all the data is sent to the client and loaded, but its not all put into the DOM (HTML)