Forums Ignite UI for jQuery
Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Close
Spark
510
I found that each grid data request loads Database 4 times, can I avoid so many times DB query?
posted

I found that each grid data request will query database 4 times. Can I avoid so many times DB query?

I use the LinqToSql solution for grid data source. And my Sql executing is very slow (each sql will take about 10 seconds).

I found that each grid load request takes about 40 seconds, it’s unacceptable.

When I dive into the code. I found you load the data source 4 times in following functions:

  1. GridModel.GenerateColumns()
  2. GridModel.CheckNullable()
  3. GridModel.GenerateLayouts()
  4. GridModel.RenderFlatQueryable()

I understand that the data query in GridModel.RenderFlatQueryable() is necessary, but can we avoid data load in other 3 functions? Or do you have other solutions for my situation?

This is a very urgency issue which need you help!

Thanks.

Parents
No Data
Reply
  • 6279
    posted

    Hi Spark,

    This is a tough question with a lot of factors involved so I'm afraid that there is no simple answer.

    The best way to begin the investigation would be to see the way your grid is configured and how it's receiving the IQueryable (your LINQ2SQL medium to your database).
    If possible, can you please provide us with a sample MVC project where you are able to reproduce the issue?

    spark_li said:
    I found that each grid data request will query database 4 times. Can I avoid so many times DB query?
    Keep in mind that these should be fast SELECT TOP 1 queries which cannot be avoided.
    However, in order to avoid guessing, can you provide us with a trace file of SQL profiler so we can inspect it?
    (A screenshot of the queries made by the grid using the SQL profiler will also suffice) 
    spark_li said:
    I found that each grid load request takes about 40 seconds, it’s unacceptable.
    This smells like bad table indexing and/or very slow network access to your database - there should be only one query made by the igGrid that would require a substantial amount of time to execute: the data retrieval. And it alone passes fairly quickly. I've personally tested it with a grid which had some features (paging and I forgot what the others were ...) and it was bound to a table with 16 000 rows.
    Another community member in the forums had asked for an inspection so this was the scenario and the outcome was acceptable - several milliseconds. 

    Thus, I can say that the grid performs only the bare minimum of SQL queries if that grid is configured well enough and we cannot say that there's problem unless we inspect the scenario.

    Hope this helps,
    Borislav 

Children