Eloquent Typecasting to nvarchar(4000)

Published 5 months ago by timroush

In our DB monitoring, we typically see Eloquent casting string variables to nvarchar(4000) when building its queries. This can cause it to bypass an index on a varchar column since it has to do the implicit type cast.

Has anyone run into this before, and is there a way to default it to either not try to cast strings, or to define what they should be cast to, so that we can make use of our indexing?

jlrdw
jlrdw
5 months ago (252,690 XP)
timroush

I think I have my columns cast correctly, but I think the issue I'm possibly running into is that in the SqlServerGrammar, it shows

protected function typeString(Fluent $column)
{
    return "nvarchar({$column->length})";
}

with no way to tell it to use a varchar instead.

Cronix
Cronix
5 months ago (798,990 XP)

I think you'd be better off using text datatype instead of varchar(4000).

You can also manually add raw queries to force using an index, but that seems a bit much, especially if you're doing it a lot.

jlrdw
jlrdw
5 months ago (252,690 XP)

Just me, but for sql server I'd use asp.net razor with c#

I had things like a varaible passed in querystring for example and in c# you have to cast it:

myvar = Convert.ToDouble(pg);

But I don't understand your situation, just try and use a datatype that will work correctly. Or work with a string (if possible) then cast.

I have always looked at life like:

c# goes with sql server 
php goes with mysql
java goes with any supported database, but mysql or sap is a good marriage.

Ok enough of my thoughts on this.

timroush

@jlrdw honestly, I've never had issues with PHP and SQL Server. SQL Server has enterprise features that I don't know of in MySQL (granted I'm not a DBA), but the other point is that in the business I'm in, I don't even have the option of choosing the database I'm using. The business was built on SQL Server long before I got here.

@Cronix I think I'm not explaining it clearly: my columns are small varchars (say 200 characters or so), but when I see the query run in the analyzer, I see it execute as:

(@P1 nvarchar(4000))select top 1 * from [table] where [column] = @P1

I think that initial casting of the bound parameter to an nvarchar(4000) is causing it to bypass my index, which is of my varchar(200) column. So the problem (I think) is that my column is appropriate, but my query is being compiled into something that is inefficient.

Perhaps I'm reading this wrong?

Cronix
Cronix
5 months ago (798,990 XP)

The only way I've seen to force Eloquent to use an index that isn't being used is to append the query.

Something like

// all of your normal stuff
$query = Model::where('this', 'that'); 

// force it to use an index
$query->getQuery()->from(\DB::raw('`table` FORCE INDEX (index_name)'));

// get the results
$results = $query->get();

I've done that with MySQL - it may be different syntax for MSSQL.

I haven't used MSSQL for a laravel project so can't really say what's going on. I know it doesn't do that with MySQL, or at least that I have seen.

jlrdw
jlrdw
5 months ago (252,690 XP)

And if regular PDO works just use regular PDO. Eloquent is not a cure-all for everything. You probably already know about laravels instance to PDO, getPdo (). Good luck.

timroush

It's all good, thanks for the discussion guys. I know this is kind of a deep quirk to work through (not a lot in our environment use MS over My). I like using Eloquent for building out these simple queries, and I mostly wanted to document this issue somewhere on the internet, as thus far I haven't really seen it called out much.

rsvb
rsvb
5 months ago (7,350 XP)

A php string should match the mssql column type, i think nvarchar is the correct type, but now it will not use the varchar index.

Please sign in or create an account to participate in this conversation.