EverSQL Database Performance Knowledge Base

Question 1

EverSQL Database Performance Knowledge Base

Get data from a Dataset by parameter in WHERE clause

Database type:

I'm following a tutorial where they explain how to add a Dataset to your ASP.NET web application and how to add a parameter to a SQL query. But it's not really working for me the way they are doing it in the tutorial.

I've added a Dataset to my App_Code folder. In the dataset i made a connection to a database in SQL Server. Now i can get data from my database by giving the dataset a SQL query. In the tutorial they do something like this:

SELECT TOP 20 [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[MakeFlag]
      ,[FinishedGoodsFlag]
      ,[Color]
      ,[SafetyStockLevel]
FROM [Production].[Product]
WHERE (Color = :Color)

The point in this example is the :Color part. That is how they let it know where to add the parameter. But when i use this query in my Dataset it gives me a warning:

Error in WHERE clause near ':'. Unable to parse query text.

After that he is able to actually add a parameter to the Dataset through the Properties window and test preview his data. That doesn't work for me since it already starts to complain when i add my query.

What can i do to solve this problem?

Answer

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX product_idx_color ON Production.Product (Color);

The optimized query:

SELECT
        TOP 20 [Production].[Product].[ProductID],
        [Production].[Product].[Name],
        [Production].[Product].[ProductNumber],
        [Production].[Product].[MakeFlag],
        [Production].[Product].[FinishedGoodsFlag],
        [Production].[Product].[Color],
        [Production].[Product].[SafetyStockLevel] 
    FROM
        [Production].[Product] 
    WHERE
        (
            [Production].[Product].Color = :Color
        )

Question 2

EverSQL Database Performance Knowledge Base

Get data from a Dataset by parameter in WHERE clause

How to optimize this SQL query?

The optimization process and recommendations:

Optimal indexes for this query:

The optimized query:

Related Articles