Defining SQL Parameter type as varchar instead of nvarchar

ctran2

Hi-

We're currently using Editor 2.0.5, Net Core 6, and a SQL Server database. In our controller, we've added a where clause to filter our datatable. Upon profiling with SQL Server Profiler, we noticed that a stored procedure is being executed where DatasetPeriod parameter is defined as an nvarchar, taking more than 1500 ms to execute.

Surprisingly, when we experimented by replacing nvarchar with varchar, the query ran about 10 times faster (about 150 ms). We're curious if there are any suggestions or best practices within Editor that could allow us to define the parameter within queries sent to SQL Server as varchar instead of nvarchar.

Here's our controller:

public ActionResult OtpProvisionComparison()
{
    using (var db = new Database("sqlserver", cnStr))
    {
        var editor = new Editor(db, "OtpProvisionComparison", "OtpProvisionComparisonId")
            .Model<OtpProvisionComparisonModel>()
           // .Field(new Field("DatasetPeriod").DbType(System.Data.DbType.AnsiString)) // Tried setting DatasetPeriod as AnsiString but failed to work

          .Where("DatasetPeriod", Request.Form["datasetPeriod"], "=")
          .Process(Request).Data();

        return new JsonResult(editor);

    }
}

Query retrieved from SQL Server Profiler where DatasetPeriod is defined as nvarchar:

exec sp_executesql N'SELECT  * FROM  [OtpProvisionComparison] WHERE [DatasetPeriod] = @where_0 ',N'@where_0 nvarchar(19)',@where_0=N'2024-01-30 21:17:00'

Any insights or recommendations of optimizing this aspect of our setup would be greatly appreciated. Thanks in advance!

allan

I'm surprised as well! However, I'm not quite clear on how the Editor API comes into play here - nvarchar or varchar will be defined when the stored procedure is created (or the table defined ). Is it the N'string' in the query you want to define in with Editor? If so, I'm afraid there is no way to do that at the moment, it would require a modification to the .NET libraries for Editor (which are open source).

Allan

ctran2

Hi Allan,

Thank you for your reply. After doing some research, I found that .NET strings are unicode, making the parameter passed as nvarchar. If I want it as varchar, I need to pass it as ANSI string. I'm curious to see if these conditions can be added somewhere to Editor dll:

 if (param.SqlDbType == SqlDbType.NVarChar)
                {
                    param.SqlDbType = SqlDbType.VarChar;
                }
                if (param.SqlDbType == SqlDbType.NChar)
                {
                    param.SqlDbType = SqlDbType.Char;
                }

I saw a forum post where you resolved a similar issue by adding conditions to param.DbType within
DataTables-Editor-Server/DataBaseUtil/Postgres/Query.cs. I'm wondering if this can be done with SqlServer/Query.cs as well.

Here's the link to that forum post:
https://datatables.net/forums/discussion/74619/postgressql-can-not-input-decimal-type
Github commit details:
https://github.com/DataTables/Editor-NET/commit/3e470ad9258c59010320de3135d22847c9f4bad2

Thanks again!

allan

Hi,

Certainly open to improving the Editor .NET libraries! I'm actually not certain where you are proposing the change either - is that in the where condition binding?

You can change the DbType already through the Field.DbType() method, which is used on like 198 there.

That wouldn't impact Where conditions though, which it looks like it your main area of concern?

The Postgres issue was for stricter data types when writing to a field, which I think is a little different from this case.

Allan

ctran2

Hi Allan,

Apologies for any confusion. In our controller code from the original post, line 9, we're dealing with the WHERE clause that takes a DatasetPeriod string from user input. This string is then used as a parameter in a SELECT statement to query the database. This is the SELECT statement tracked by SQL Server Profiler when we load the page:

exec sp_executesql N'SELECT  * FROM  [OtpProvisionComparison] WHERE [DatasetPeriod] = @where_0 ',N'@where_0 nvarchar(19)',@where_0=N'2024-01-30 21:17:00'

It shows that DatasetPeriod parameter is sent as an nvarchar.
However, we intend for DatasetPeriod to be a varchar, expecting a query like this to be sent to SQL Server instead:

exec sp_executesql N'SELECT  * FROM  [OtpProvisionComparison] WHERE [DatasetPeriod] = @where_0 ',N'@where_0 varchar(19)',@where_0=N'2024-01-30 21:17:00'

Could you guide us on where in our code we can explicitly define DatasetPeriod as a varchar before sending it to filter our database?

ctran2

Hi @allan,

Just following up from last weeks' post, was wondering if there were any suggestions on how to modify the parameter datatype.