Performance and using of VARCHAR boxes as filters in SSRS reports

Forrards
March 18, 2023

Very few people have thought about this performance killer. It is all about report parameter of text type.

The initial situation

There was a table with about 45 million rows. Some columns in the table were VARCHAR and should have been filtered. As a result of filtering by 10 different parameters the total number of rows could have been significantly reduced. SQL Server was going for table scan in this case. Also there was a number of indexes defined on the table.

The where-clause based on a particular parameter was used:

Analysis:

In case you, for example, run the statement above and look into Activity Monitor, you will see what query SSRS is really running:

At this moment the ranking by data types come into arena. As you can see data type with the lower precedence is implicitly converted up to the data type with the higher precedence.. In this case, there were also varchar (in the table) and nvarchar (as a parameter). Now the list is presented inconveniently, because the smallest number means the highest data type. Here we should note that varchar fields are converted to nvarchar.It would have been better to declare some of the parameters as varchar, but this improvement hasn’t been implemented in Reporting Services yet.

Solution

There are several ways to change the way of this conversion. Here I will describe the method that suits me most.

Dynamic dataset query

We build a dataset query at run time by ourselves, which means that we have to define the fields entirely by hand.
Thus, the dataset begins with

ssrs1

How to build a command for a comma-separated list, where there is no capital N standing before a string and the values are not of nvarchar type.The command looks as follows:

In case we have other parameters, for example numeric data, the appropriate command needs to be written and it is not difficult:

ssrs2

With this dynamic dataset now we can run correct queries in terms of performance and perform Index Seek instead of Index Scan.The conversion of the data type into nvarchar in the table was out of the question, since the space used increases and fewer records fit one page, but data compression can improve this.A good alternative would be to replace the strings with numeric values and run the queries only with numeric parameters. But for this purpose you must create a small data warehouse with the corresponding dimension tables and rebuild the data.

Forrards
March 18, 2023
Share
Recent articles:
Database consultancy: SaaS database considerations
March 31, 2023

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

More
Database consultancy: tree-like structure
March 31, 2023

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

More
Database consultancy: Hierarchy in MS SQL Server
March 31, 2023

In our time of greatest prevalence of databases were relational databases, which are the main targets of the tables and the relationships between them. Tables can solve most problems for data storage and manipulation. But in the real world entity requiring storage is not always presented in a tabular form. One of these very common types of data structures other than the table is a tree structure, where each data element is the parent and the offspring. An example of such a structure may be the structure of state enterprises, which is headed by the director (the root of the tree), his deputies, heads of departments from which are subject to certain deputies, employees of departments, which are subject to the rulers.

More

Subscribe to our blog

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Database consultancy: SaaS database considerations

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

March 31, 2023
Database consultancy: tree-like structure

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

March 31, 2023

Discuss your project with us

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.