This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at firstname.lastname@example.org
We use Lightspeed v5.0 with SQL Server 2008 R2 database and are experiencing some performance issues with certain queries. In our database all text columns are stored as varchar.
If we perform a query using lightspeed that searches based on a text value, i.e.:
The generated SQL query uses nvarchar type for the 'search' parameter. SQL then performs an implicit conversion on this when searching and will not use seek on the indexes.
Unfortunately we have some legacy tables where we have text based keys and this causes the queries to run a lot slower than if the parameters were varchar.
I saw a similar post from July 2016 where a custom build was offered to resolve this issue as adding in some overridable options would take too long:
Has this feature (i.e. generating varchar parameters) been implemented? If not would it be possible for us to get a similar custom build to resolve this.
No we have not implemented this change as its not compatible with the other database providers we support. If you ping me offline via support at mindscape.co.nz I can send you through the details of the changes you will need to make in the source code to achieve this specific for SQL Server though.