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 support@mindscape.co.nz
|
I have an application using Lightspeed which is running very very slow in production. I ran the Profiler in SQL Server, and checked the queries. As an example, I have a simple query against a single indexed field which was taking 3 seconds. I ran the same query interactively and it took 2 milliseconds. The parameter being passed to the spExecuteSQL for the key value is type nvarchar, while the actual field is varchar. When I change the parameter to varchar, the spExecuteSQL runs instantly. I was hoping to take advantage of the time difference to point my programmers in the right direction when they look at it. I don't know if there is a universal default that can be set, or if it's the way they are calling LightSpeed. The dev server is so fast that we didn't notice, but we rolled out the app last week, and now all hell has broken loose, with 20 seconds waits for the user. Normally I would wait for the specifics from my programmers, but New Zealand will be wrapping up the day before they start in Canada. |
|
|
|
|
My programmer tells me there is no way to specify a varchar parameter, they all go as nvarchar, which then does not use the index on the field. Is this correct? Seems like a unimaginable limitation. |
|
|
|
|
Hi, We'll have a look into this to see if there's an option to force the varchar. As a general note, we infer the types so your programmer is not wrong that we don't tie things directly to to the database (it is after all an ORM, meant to abstract from database specifics, but I totally see your point here where an override would be valuable). The lead engineer on LightSpeed is on vacation at present but I will see if one of the other team members can help in the meantime. Kind regards, John-Daniel Trask |
|
|
|
|
Mr. Trask, please have someone fix the download capability on the web site. I have thrice reported it over the last week or so with no reply and I see someone else posted the problem in the LightSpeed forum 21 days ago. Thanks. WordTickler |
|
|
|
|
It would be best if you automatically used varchar when the index field(s) are vchar. SQL will do an index scan instead of a lookup with the mismatched datatypes. This impacts every indexed character field. We could change everything to nvarchar, but we don't need unicode and don't really want to double the storage required. |
|
|
|
|
At this stage the quickest approach to solving this will be to create a custom build of LightSpeed forcing this change in behavior globally. Can you please email me at support at mindscape.co.nz and I can give you some more details on how to approach making this change.
|
|
|
|
