Building SQL with foreach

Question

I have this code:

private void button1_Click(object sender, EventArgs e)
{
    foreach(Int_String partner in partneri)
    {
        double danaBezProdaje = (DateTime.Today - Komercijalno.Partner.PoslednjaKupovina(partner._int)).TotalDays;
        if (danaBezProdaje > 31 && danaBezProdaje < 1100)
        {
            NeaktivniPartner np = new NeaktivniPartner();
            np.ppid = partner._int;
            np.naziv = partner._string;
            np.danaBezKupovine = danaBezProdaje;
            neaktivniPartneri.Add(np);
        }
    }
    dataGridView1.DataSource = M.List.ConvertToDataTable(neaktivniPartneri);
}

So in here i have List<Int_String> partneri which contains 5k rows. For each of it i run Komercijalno.Partner.PoslednjaKupovina(partner._int) which contains SQL statement which looks like this:

public static DateTime PoslednjaKupovina(int ppid)        
{
    using (FbConnection con = new FbConnection(M.Baza.connectionKomercijalno2018))
    {
        con.Open();
        using (FbCommand cmd = new FbCommand("SELECT DATUM FROM DOKUMENT WHERE PPID = @PPID ORDER BY DATUM DESC", con))
        {
            cmd.Parameters.AddWithValue("@PPID", ppid);

            FbDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
                return Convert.ToDateTime(dr[0]);
            else
                return new DateTime(1, 1, 1);
        }
    }
}

So SQL statement is executed 5k times which is way too slow.

So how could i pass my List<Int_String> or just array of ints to sql command so there i execute one time (inside foreach or how else) and return dataAdapter so i execute SQL only once.

DataAdapter returned should look like this:

| ppid     | datum    |

So let's say i have Table PARTNER (from which i populate List partneri with SELECT PPID, NAZIV FROM PARTNER) with this data in it:

| PPID    | NAZIV    |
| 1       | name001  |
| 2       | name002  |
| 3       | name003  |
| 4       | name004  |

And let's say my DOKUMENT table has this data:

| ID      | PPID    | DATE       |
| 1       | 2       | 12.03.2018 |
| 2       | 3       | 12.03.2018 |
| 3       | 2       | 05.03.2018 |
| 4       | 2       | 03.04.2018 |
| 5       | 1       | 26.03.2018 |
| 6       | 4       | 21.02.2018 |
| 7       | 4       | 06.05.2018 |

And output i want is:

| PPID    | DATE       |
| 1       | 26.03.2018 |
| 2       | 03.04.2018 |
| 3       | 12.03.2018 |
| 4       | 06.05.2018 |

Not a good method @Aria. The parameter string may get an error at the limit and a bad performance this way.. — Hüseyin Burak Karadag, May 16 '18 at 06:48
@HüseyinBurakKaradag, I thought the limitation is more than `5k` , but I don't know the limitation exactly, but it can be separates block of `in` ? — Aria, May 16 '18 at 06:53
@AleksaRistic Where do you fill the 'partneri' data ? If you get data on DB , why use join DOKUMENT table ? if your answer is no , my solution is, Use the 'bulk insert' method to insert the ids data into the session temp table and join it with the document table. This is much faster — Hüseyin Burak Karadag, May 16 '18 at 06:54
@HüseyinBurakKaradag i populate `partneri` at start of my program with background worker which `SELECT * FROM PARTNER` which is in same database file as `DOKUMENT` — Aleksa Ristic, May 16 '18 at 07:07
@AleksaRistic, then `join` them like `select * from PARTNER JOIN DOKUMENT ON PARTNER ._int = DOKUMENT.PPID` — Aria, May 16 '18 at 07:11
@Aria problem is that my function `PoslednjaKupovina` returns only latest date (first row). So when i join tables i will get about 10k (500k total) rows for each partner but i only need one of each and that one's date need to be closest to today. — Aleksa Ristic, May 16 '18 at 07:20
@AleksaRistic, Yes if you join them you will get many rows in this case you should change your cod, iterates that rows each of row is equal to `PoslednjaKupovina` running , in the other words you get `5k` rows in one shoot instead of running `PoslednjaKupovina` 5k times. — Aria, May 16 '18 at 07:25
Please post the DDL of relevant tables, sample data and expected result. I agree with Aria that using a join will be a lot better than executing individual selects (the so-called N+1 problem). — Mark Rotteveel, May 16 '18 at 08:17
@MarkRotteveel I have updated question with example database data and expected result. What does DDL mean? — Aleksa Ristic, May 16 '18 at 08:31
DDL = Data Definition Language, so the create table statements, etc. — Mark Rotteveel, May 16 '18 at 08:36
`select ppid, max(datum) from dokument group by ppid` ? Can there be values in DOKUMENT.PPID that are missed from PARTNER.PPID ??? If that is normal FOREIGN KEY then there won't be any. Or, to address Aleksa's later wish about days counter instead of dates, `select ppid, current_date - cast(max(datum) as date) from dokument group by ppid` according to http://www.firebirdtest.com/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-datatypes-datetime.html#fblangref25-datatypes-datetimeops — Arioch 'The, May 16 '18 at 09:08

score 1 · Accepted Answer · answered May 16 '18 at 08:35

1

You can replace all your logic with a single query that does:

select d.ppid, max(d.datum)
from partner p
inner join dokument d
    on d.ppid = p.ppid
group by d.ppid

You may need to add a where-clause with additional conditions to select which partners you want to have.

answered May 16 '18 at 08:35

Mark Rotteveel

100,966
191
140
197

One more thing, instead of `max(d.datum)` i want to be `(DateTime.Today - max(d.datum)).TotalDays. How i can accomplish that inside query – Aleksa Ristic May 16 '18 at 08:49
@AleksaRistic You should consider leaving that in your C# code, but otherwise consider wrapping the select with another one, eg something like `select ppid, datediff(day, datum, current_date) datedifference from ( – Mark Rotteveel May 16 '18 at 08:54
why nested selects? can not he put this subtraction directly into top-level select? And, perhaps, I would send `DateTime.Today` as a parameter from client. So that the server would treat it as constant, and because local datetimes can be different on client computer vs server computer – Arioch 'The May 16 '18 at 09:00
And perhaps in this specific case I would not use `DateDiff` but directly use `DATE - DATE` subtraction, typecasting `datum` column to the type if needed. http://www.firebirdtest.com/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-datatypes-datetime.html#fblangref25-datatypes-datetimeops – Arioch 'The May 16 '18 at 09:02
Can there be values in `DOKUMENT.PPID` that are missed from `PARTNER.PPID` ??? If that is normal FK then I guess this query should be stripped of references `PARTNER` table altogether! You just do not need it, you can aggregate on `DOKUMENT` table alone with no any join at all. – Arioch 'The May 16 '18 at 09:07
@Arioch'The i get the point but i need joining because i will not always want all PPID but maybe only `PPID 1, 11 and 68` – Aleksa Ristic May 16 '18 at 09:12
@AleksaRistic but I think your `PARTNER` table contains ALL the partners, not only `1,11,68` pre-selected to fetch, doesn't it ? and please, show table declarations (DDL, SCHEMA, create table statement - here it all means the same), because no one knows what type your DATUM column has, what are SQL-level relations between those two tables (are them FK or not), what are other constraints, etc – Arioch 'The May 16 '18 at 09:13
It also contains column `VIP` and only `1, 11 and 68 have VIP = 1` so when i want to check date for VIP guys i need to filter it – Aleksa Ristic May 16 '18 at 09:16
Mark Rottenveel's answer is working with addition of `datediff` – Aleksa Ristic May 16 '18 at 09:17
if the list of partners to check is always/mostly short, you can just pass it to the query. `select ppid, current_date - cast(max(datum) as date) from dokument group where ppid in (1,11,68) by ppid` or see that another question that I linked. Or you may modify Mark's answer with `....inner join dokument d on d.ppid = p.ppid and p.VIP=1`. Frankly, in Mark's query it would be more consistent to swap PARTNER and DOKUMENT tables. Not that it would change how the server sees it, but from human standpoint it is D which is primary data source and P is only used for secondary checks(filtering) – Arioch 'The May 16 '18 at 09:17
1

@AleksaRistic overall in this answer's comments you added two points, that are missed form the question itself. That you need days count rather than dates, and - important one - that your actual filtering is not some arbitrary, random list of IDs but the REAL criterion is `PARTNER.VIP=1`. You omitted the crucial information - what you do really want - from the question, and you was getting answers poorly tuned to your REAL problem - https://meta.stackexchange.com/questions/66377 /// Your points should be listed in the Question itself, not comments!!! Comments are noisy, hard to find there. – Arioch 'The May 16 '18 at 09:24
@Arioch'The I used nested select because I find it more readable, and I recall having issues with doing something like that with aggregate functions, but I can't recall if it was with Firebird or another database system. – Mark Rotteveel May 16 '18 at 11:13

score 0 · Answer 2 · answered May 16 '18 at 08:54

Making it as an answer, for there is already very many comments below the question, so it would just be lost in the noise there.

So how could i pass my List or just array of ints to sql command so there i execute one time

That question was discussed here: Filter sql based on C# List instead of a filter table

Depending upon how typical(frequent) is the query and how large is the data there are few ways.

Global Temporary Tables
Imitation of GTT using UNION ALL and several select {constants} from RDB$database
Flattening the list into one long string of special format and using reversed LIKE matching.

PS. For your specific task Mark's answer is definitely much better: you do not have to fetch lot of data from the server only to pass it back in another query. I just wanted to link a question, related to a part of this one, that was already answered.

Building SQL with foreach

2 Answers2

Linked