0

I have a dashboard which displays 5 live totals calculated using 5 separate SELECT count (*) queries. The result of these is inserted into the page with

       Using rdr As SqlDataReader = db.ExecDataReader(qry1)
            rdr.Read()
            qa1.InnerText = rdr(rdr.GetName(0))
        End Using

        Using rdr As SqlDataReader = db.ExecDataReader(qry2)
            rdr.Read()
            qa2.InnerText = rdr(rdr.GetName(0))
        End Using

Is there any way to reduce load time? I tried joining all the queries with UNION and in SQL SMS it takes a few seconds off the time, but I don't know how to pull each 'row' from the result.

That said, it is still 12 seconds in Server Management Studio which is still undesirable for a dashboard. Are my hands tied by our infrastructure?

user1765369
  • 1,333
  • 3
  • 11
  • 19
  • 3
    Well you've identified the cause of the slow response time - the queries are costly. Your first port of call is to try to optimise those queries - check for missing indexes etc. With this in mind, the question is too vague currently to help with that. – AdaTheDev Nov 10 '16 at 15:12
  • Create one usp which contain all the 5 query and this usp will return you data set which is the collection of 5 tables and then you can use these table for further process . – Anurag_Soni Nov 10 '16 at 15:13
  • Do you need the results to be 100% accurate? Are your tables being updated or only inserted? do you use LOBs? – David דודו Markovitz Nov 10 '16 at 16:29
  • Possible duplicate of [SQL Server Count is slow](http://stackoverflow.com/questions/12479677/sql-server-count-is-slow) – sgmoore Nov 10 '16 at 16:55

2 Answers2

0

It sounds like the count queries need to be optimised. If they're running that slowly, either you've got a LOT of data to count, or there's a complex query that's improperly optimised.

You'd need to post some more details about the table structure and the queries that your using for us to be able to help with what the possible cause of the slow queries might be.

Tim
  • 4,217
  • 1
  • 15
  • 21
0

Cache the counts, in memory, or in a stage table. This will optimize the dashboard call. Next find the most efficient way to update your stage based on your requirements. Maybe update the stage when a new item is added or create a background process that updates the stage in intervals.

MIKE
  • 1,039
  • 7
  • 24