MS SQL Extra Slow

My solution uses the MBS SQL plugin. I had to use it because there were some odd issues around MS SQL Blobs.
Regardless, it has been working great, and performing well with the exception of MS SQL. I have not done much to optimize the database through indexes, but it’s performed Weill even with large databases using MySQL and others. MS SQL is extra slow. I’m suspicious it’s because I have a large text column and that there is something I need to do to either on the MS SQL Server side, or in the MS SQL Connection to support this better.

Suggestions?

Well, you have some details?

just what I said above I have a column varchar(max) called data.
At one point I do a select data from that table and it hangs for minutes despite there not being that much data. On other database servers it loads fast.

I’m wondering if I need to alter the packet size in the server settings.

Well, no idea what settings you use.
MS SQL with ODBC, OLE DB or DB Library?

Do you use “PreFetchRows” to download rows in batch?

A chunk of it here:

Dim libtdsodbc As Folderitem = app.ExecutableFile.parent.parent.child("Frameworks").child("libtdsodbc.0.dylib")
cs = "DRIVER=" + libtdsodbc.NativePath + _
";Server="+host+";UId="+User+";PWD="+Pass+";Database="+DatabaseName+";TDS_VERSION=7.2;Port="+Port '

con.Option("UseAPI") = "ODBC"
con.Option("ODBC.LIBS") = "/usr/lib/libiodbc.2.dylib"
con.DatabaseName = "ODBC:"+cs

I do not use PreFetchRows. I need to read up on this.

You may also log how long what takes.
e.g. the SQL Execute/Select and how much it takes to loop over records.

My own experiences with Xojo and MS SQL via ODBC are limited but the conclusion is that all selects do not make use of indexes despite the fact indexes on tables are present. It was better for me to import tables into SQLight on the beginning…

Still too vague. Without the table schema and the query one can only guess. That the query runs acceptably on some other database flavour is of little interest. Other database engines may process similar queries differently.

Having said that any SELECT * against an MSSQL table with a wide column is a prime suspect for a slow response. Also worth looking at the impact of any OR operations in query criteria.

That is the sort of thing people do when they are guessing. Never say never but poor query strategy is a far more common cause of poor MSSQL response.

First try to isolate the fault at the server, network or application using the Query Optimiser. A query that responds slowly when run directly on the server will not be made any faster by messing with the network or application.

1 Like