How can i speed this query linq please?

Question

I want to make the LINQ query in this method faster:

public string GeneraCodiceListaEventi(DateTime data)
{
    string codice = String.Empty;

    string[] range1 = new string[] { "08:00", "08:30", "09:00", "09:30", "10:00", "10:30", "11:00", "11:30", "12:00", "12:30", "13:00", "13:30", "14:00", "14:30", "15:00", "15:30", "16:00", "16:30", "17:00", "17:30", "18:00", "18:30", "19:00", "19:30", "20:00" };
    string[] range2 = new string[] { "08:29", "08:30", "09:29", "09:59", "10:29", "10:59", "11:29", "11:59", "12:29", "12:59", "13:29", "13:59", "14:29", "14:59", "15:29", "15:59", "16:29", "16:59", "17:29", "17:59", "18:29", "18:59", "19:29", "19:59", "20:29" };

    using (DatabaseDataContext contestoDB = new DatabaseDataContext())
    {
        contestoDB.ObjectTrackingEnabled = false;

        for(int i=0; i<25; i++)
        {
            var eventi = (from db in contestoDB.Eventi
                          where db.DataPrenotazione.Date == data.Date && (db.DataPrenotazione.TimeOfDay >= TimeSpan.Parse(range1[i]) && db.DataPrenotazione.TimeOfDay <= TimeSpan.Parse(range2[i]))
                          select new
                          {
                              ID = db.ID,
                              IDCliente = db.IDCliente,
                              Note = db.Note,
                              Ore = db.DataPrenotazione.ToShortTimeString()
                          });

            if (eventi.Any())
            {
                codice += "<li><span class='ora'>" + range1[i] + "</span><input type='checkbox' id='item-" + GetNumItem(range1[i]) + "'/><label for='item-" + GetNumItem(range1[i]) + "'>Espandi</label><ul>";

                foreach (var e in eventi)
                {
                    codice += "<li class='app'> " + e.Ore + " - " + GetNominativoClienteDaID(e.IDCliente) + CheckNota(e.Note);
                }

                codice += "</ul></li>";
            }
            else
            {
                codice += "<li><span class='ora'>" + range1[i] + "</span>" + noapp + "</li>";

            }
        }

    }

    return codice;
}

In this function I'll build a string to send to html using ajax and show in the browser. But how can I make the query faster? Is there an alternative?

Which part of your query is running slowly? First guess, try to avoid using string concatenation in loops -- look into something like the StringBuilder classes. — TZHX, Jan 11 '16 at 09:46
Don't load the entities from the db during each loop, construct one query, get all the results and then iterate and create the html. — 3dd, Jan 11 '16 at 09:47
`GetNominativoClienteDaID`, `CheckNota`, `GetNumItem`... do they go to the database as well? Apart from that `foreach` and `.Any()` query the database twice, and the outer `for`, initiates 25 queries. Try to reduce that. My guess is that this part of code queries the database for 150 times already. Also, the range of `"08:30"` is broken. — Caramiriel, Jan 11 '16 at 09:47
For performance related queries post your code at [CodeReview StackExchange](http://codereview.stackexchange.com/) — Suprabhat Biswal, Jan 11 '16 at 09:48
Ok. i'll post my tables and their fields soon.... thanks for tricks... wait and i'll put soon them. — Markus Werner, Jan 11 '16 at 09:51
Not that it'll make the query faster, but it looks like you have a small bug... based on the pattern, the second member of `range2` should be `8:59` not `8:30`. — gerrod, Jan 11 '16 at 09:52
Whats even faster than using stringbuilder as many have suggested, is to write directly to the response stream. — Janne Matikainen, Jan 11 '16 at 10:04

score 1 · Accepted Answer · edited May 23 '17 at 11:52

1

i can give you at least 2 suggestion to get this method execute faster:

1) Instead of using 'string codice = String.Empty;' use a StringBuilder instead, like this: 'StringBuilder longString = new StringBuilder();' You might need to add System.Text to the using references on top of your code file. StringBuilder is much faster then using a common string because of many reason you can read up here: String vs. StringBuilder

2) Instead of using 2 arrays of string and then parsing those string EACH TIME You loop into Timespan objects, you should instead create 2 arrays of TIMESPANS. At the moment, you are converting strings to timespan 2 times (2 array) * 25 times (your loop), so, those are 50 conversion you don't need to do.

This is how you could slightly optimize your code.

Then, to optimize the db access, you should do only 1 query with all the results, and then building the html splitting the results through code. More queries = more time

edited May 23 '17 at 11:52

Community

1
1

answered Jan 11 '16 at 09:57

Fabio

48
1
9

Doesn't the compiler already use string builder when concatenating strings? – Caramiriel Jan 11 '16 at 10:26
@Caramiriel If so, there would be no performance advantage in using StringBuilder class, but if you make a test, you will notice there is a HUGE performance difference. Also, in my past i tried to build a BIG string using 'string', and i got a stackOverFlow exception, which is not given that easy with StringBuilder class. – Fabio Jan 11 '16 at 10:38
Grazie Fabio! Thanks Fabio! – Markus Werner Jan 11 '16 at 11:41

score 0 · Answer 2 · answered Jan 11 '16 at 09:59

Other than 3dd's point, the speed of this query has almost nothing to do with your C# code, and everything to do with the database. If Eventi and DataPrenotazione are both large and not correctly indexed, then the query will run slowly. It is best explored in SQL, and analysed with whatever tools your database provides for understanding query performance.

How can i speed this query linq please?

2 Answers2