Case of the SQL vs Access Sort Order

A legacy ACCESS database had data migrated from an Access 2000 database application into SQL Server, I needed to migrate some code outside Access to .NET platform.

However I came across an issue when executing certain SQL Queries from .NET using SqlConnection/SqlCommand/SqlDataReader – even though the Access database pointed to the exact same SQL server, the exact same queries returned results in a different order.

Normally this might not be a big deal…but in this case it was, due to a legacy of the awesome application design, a hashing algorithm  needed the search results to come in the  exact same order that Microsoft Access was returning them. If I couldn’t fix the search result order it was going to be days reversing spaghetti code logic…

I was testing on my own laptop with SQL Express so didn’t have access to SQL Profiler. Instead I downloaded the open source ExpressProfiler from http://expressprofiler.codeplex.com/

I started tracing and ran the query from Access

SELECT SessionsTN3270.SessionID, SessionNames.SessionName, SessionsTN3270.SessionTypeID, SessionsTN3270.UserNameID, SessionNames.Host, SessionNames.Port, LUs.LU, SessionNames.LastModifiedSN, Templates.Template, Templates.TemplateType, SessionNames.SessionType

FROM (SessionNames LEFT JOIN Templates ON SessionNames.Template = Templates.TemplateID) INNER JOIN (SessionsTN3270 LEFT JOIN LUs ON SessionsTN3270.SessionID = LUs.SessionID) ON SessionNames.SessionNameID = SessionsTN3270.SessionNameID WHERE UserNameID = 3978 ORDER BY Templates.TemplateType, SessionNames.SessionType;

 

I would also point out the the ORDER BY statement was almost effectively useless as almost all results had the same TemplateType and SessionType.

The Access SQL query was run using the following method:

image

The same query, connected to same SQL server, showed different result order:

Microsoft Access SQL Management Studio
image image

Looking back at Express Profiler we could see the activity this SELECT statement caused when run from SQL Management Studio:

image

However Microsoft Access generated a whole lot more activity for the same query – slightly less efficient with 678 reads (Access) vs 52 reads (SQL Management Studio)

image

The actual SQL code expanded out to…

SELECT "dbo"."SessionsTN3270"."SessionID" ,"dbo"."SessionsTN3270"."SessionID","dbo"."SessionNames"."SessionType" ,"dbo"."SessionNames"."SessionNameID","dbo"."Templates"."TemplateType" ,"dbo"."Templates"."TemplateID" FROM "dbo"."SessionsTN3270",\oj "dbo"."SessionNames" LEFT OUTER JOIN "dbo"."Templates" ON ("dbo"."SessionNames"."Template" = "dbo"."Templates"."TemplateID" ) \ WHERE (("dbo"."SessionsTN3270"."UserNameID" = 3978 ) AND ("dbo"."SessionNames"."SessionNameID" = "dbo"."SessionsTN3270"."SessionNameID" ) )
go
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "SessionID" ,"dbo"."LUs"."LUID" FROM "dbo"."LUs"  WHERE ("SessionID" = @P1)',17797
select @p1
go
exec sp_execute 1,17798
go
exec sp_execute 1,17799
go
exec sp_execute 1,17800
go
exec sp_execute 1,17801
go
exec sp_execute 1,105372
go
SELECT CASE DATABASEPROPERTYEX( DB_NAME(), 'Updateability') WHEN 'READ_ONLY' THEN 'Y' ELSE 'N' END
go
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int',N'SELECT "TemplateID","Template","TemplateType"  FROM "dbo"."Templates"  WHERE "TemplateID" = @P1 OR "TemplateID" = @P2 OR "TemplateID" = @P3 OR "TemplateID" = @P4 OR "TemplateID" = @P5 OR "TemplateID" = @P6 OR "TemplateID" = @P7 OR "TemplateID" = @P8 OR "TemplateID" = @P9 OR "TemplateID" = @P10',15,15,15,15,15,33,33,33,33,33
select @p1
go
declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int',N'SELECT "SessionNameID","SessionName","Host","Template","SessionType","Port","LastModifiedSN","SSMA_TimeStamp"  FROM "dbo"."SessionNames"  WHERE "SessionNameID" = @P1 OR "SessionNameID" = @P2 OR "SessionNameID" = @P3 OR "SessionNameID" = @P4 OR "SessionNameID" = @P5 OR "SessionNameID" = @P6 OR "SessionNameID" = @P7 OR "SessionNameID" = @P8 OR "SessionNameID" = @P9 OR "SessionNameID" = @P10',204,203,202,201,200,272,272,272,272,272
select @p1
go
declare @p1 int
set @p1=3
exec sp_prepexec @p1 output,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int',N'SELECT "SessionID","SessionTypeID","UserNameID","SessionNameID"  FROM "dbo"."SessionsTN3270"  WHERE "SessionID" = @P1 OR "SessionID" = @P2 OR "SessionID" = @P3 OR "SessionID" = @P4 OR "SessionID" = @P5 OR "SessionID" = @P6 OR "SessionID" = @P7 OR "SessionID" = @P8 OR "SessionID" = @P9 OR "SessionID" = @P10',17801,17800,17799,17798,17797,105372,105372,105372,105372,105372
select @p1
go
declare @p1 int
set @p1=4
exec sp_prepexec @p1 output,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int',N'SELECT "LUID","SessionID","LU"  FROM "dbo"."LUs"  WHERE "LUID" = @P1 OR "LUID" = @P2 OR "LUID" = @P3 OR "LUID" = @P4 OR "LUID" = @P5 OR "LUID" = @P6 OR "LUID" = @P7 OR "LUID" = @P8 OR "LUID" = @P9 OR "LUID" = @P10',18539,18539,18539,18539,18539,18539,18539,18539,18539,18539
select @p1
go
SELECT "SessionTypeID" ,"SessionType"  FROM "dbo"."SessionTypes" WHERE ("HostTypeID" = 200 )
go
SELECT "UserNameID" ,"WSUserName" ,"RecordType" ,"Role" ,"SecurityLevel" ,"BuildingCode" ,"FloorLevel" ,"ServerID" ,"Airline" ,"Description" ,"LastModified"  FROM "dbo"."WSUsers"
go

 

Looking through this crap heap I notice:

exec sp_prepexec @p1 output,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int',N'SELECT "SessionID","SessionTypeID","UserNameID","SessionNameID"  FROM "dbo"."SessionsTN3270"  WHERE "SessionID" = @P1 OR "SessionID" = @P2 OR "SessionID" = @P3 OR "SessionID" = @P4 OR "SessionID" = @P5 OR "SessionID" = @P6 OR "SessionID" = @P7 OR "SessionID" = @P8 OR "SessionID" = @P9 OR "SessionID" = @P10',17801,17800,17799,17798,17797,105372,105372,105372,105372,105372

Here we see the SessionIDs in the order that access was retrieving them…

So I tried comparing a query with no JOINs:

SQL Management Studio:

image

From Microsoft Access we get the same result:

image

Well almost the same result…I noticed from Microsoft Access a numeric field was returning text :

Microsoft Access SQL Studio
image image

This value also happened to be the one in the ORDER BY statement…But this wasn’t coming from any other table. Because the ACCESS database has been ported from Access to SQL, there were basically two copies of each table in the database:

The linked table – which queried SQL

image

Plus the local table which Access used to transform the linked table:

image

So outside of application how was I going to get this query going. A table could have been added to link these settings, but changing database schema was not an option so I managed to create a SQL statement to replicate the Access result order finally. I tested it on thousands of sample data, and 100% returned data in correct order. I had to add a “case” statement to return the relevant data, plus a “DESC” to reverse the Sort Order, and also an ORDER BY SessionID.

SELECT SessionsTN3270.SessionID,
       SessionNames.SessionName,
       SessionsTN3270.SessionTypeID,
       SessionsTN3270.UserNameID,
       SessionNames.Host,
       SessionNames.Port,
       LUs.LU,
       SessionNames.LastModifiedSN,
       Templates.Template,
       Templates.TemplateType,
       SessionNames.SessionType,
       MySessionType=
        CASE SessionNames.SessionType
            WHEN '0' THEN 'Other'
            WHEN '3' THEN 'Skilling'
            WHEN '2' THEN 'Amadeus'
            ELSE 'Other'
            END
FROM (SessionNames
      LEFT JOIN Templates ON SessionNames.Template = Templates.TemplateID)
INNER JOIN (SessionsTN3270
            LEFT JOIN LUs ON SessionsTN3270.SessionID = LUs.SessionID) ON SessionNames.SessionNameID = SessionsTN3270.SessionNameID
WHERE UserNameID = 3978
ORDER BY Templates.TemplateType,MySessionType DESC,SessionID DESC

About chentiangemalc

specializes in end-user computing technologies. disclaimer 1) use at your own risk. test any solution in your environment. if you do not understand the impact/consequences of what you're doing please stop, and ask advice from somebody who does. 2) views are my own at the time of posting and do not necessarily represent my current view or the view of my employer and family members/relatives. 3) over the years Microsoft/Citrix/VMWare have given me a few free shirts, pens, paper notebooks/etc. despite these gifts i will try to remain unbiased.
This entry was posted in .NET, Microsoft Access, SQL and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s