Situation:

We have a lot of small offices around the country that are connected to the central office through ADSL link (384 kbits download, 64 kbits upload... don't ask, that's DT for you).

Adapter scenario.

Problem: The problem we have was totally unforeseen.. upload link is to slow! The generated selects have more data than retrieved data! 6 consecutive selects that fetch 1-10 rows each have 28 kbytes, and downloaded data has about 20 kbytes!

Generated select example 1: exec sp_executesql N'SELECT [COMMODO].[dbo].[dktDokumenti].[Id] AS [Id],[COMMODO].[dbo].[dktDokumenti].[Vdk_Id] AS [Vdk_Id],[COMMODO].[dbo].[dktDokumenti].[Djl_Id] AS [Djl_Id]...

Questions 1: Is there a way to remove [COMMODO] catalog part from the select statement? 2: Is there a way to remove [dbo] schema part (since we have only one schema)? 3: Is it necessary to use column aliases if we don't have inner joins or prefetch paths on the entity? 4: Is there a way to hide certain columns from the database in the LLBLGen (so that they are not mapped as entity fields)?

Generated select example 2: exec sp_executesql N'SELECT [COMMODO].[dbo].[dkvKalkulacija_Stavke].[Dokument_Stavke_Id] AS [Dokument_Stavke_Id],[COMMODO].[dbo].[dkvKalkulacija_Stavke].[Dokument_Stavk e_Kolicina] AS [Dokument_Stavke_Kolicina]...

Questions 1: Is it really necessary to use column aliases when selecting from a view? 2: Schema and catalog thing again?

If we were able to remove those two things the upload would be about 2/3 smaller that would get us through until we write web services that will do the job.

ivanc wrote:

Situation:

We have a lot of small offices around the country that are connected to the central office through ADSL link (384 kbits download, 64 kbits upload... don't ask, that's DT for you).

Adapter scenario.

Problem: The problem we have was totally unforeseen.. upload link is to slow! The generated selects have more data than retrieved data! 6 consecutive selects that fetch 1-10 rows each have 28 kbytes, and downloaded data has about 20 kbytes!

Generated select example 1: exec sp_executesql N'SELECT [COMMODO].[dbo].[dktDokumenti].[Id] AS [Id],[COMMODO].[dbo].[dktDokumenti].[Vdk_Id] AS [Vdk_Id],[COMMODO].[dbo].[dktDokumenti].[Djl_Id] AS [Djl_Id]...

Questions 1: Is there a way to remove [COMMODO] catalog part from the select statement?

Yes. See Using the generated code -> Adapter -> DataAccessAdapter functionality and then the SqlServer specific section. Use the setting 2. (which means clear the catalog name).

2: Is there a way to remove [dbo] schema part (since we have only one schema)?

In 1.0.2004.2, which is released later today (hopefully ), you can. 1.0.2004.2 is currently in RC status.

3: Is it necessary to use column aliases if we don't have inner joins or prefetch paths on the entity?

Yes, to find back the column name with the actual field read back from the datareader as the columns are named using the proper name specified in the designer, and the dataadapter won't fill a column if the names don't match.

4: Is there a way to hide certain columns from the database in the LLBLGen (so that they are not mapped as entity fields)?

Not at the moment, this will come in June.

Generated select example 2: exec sp_executesql N'SELECT [COMMODO].[dbo].[dkvKalkulacija_Stavke].[Dokument_Stavke_Id] AS [Dokument_Stavke_Id],[COMMODO].[dbo].[dkvKalkulacija_Stavke].[Dokument_Stavk e_Kolicina] AS [Dokument_Stavke_Kolicina]...

Questions 1: Is it really necessary to use column aliases when selecting from a view?

Yes, see above.

2: Schema and catalog thing again?

See above.

If we were able to remove those two things the upload would be about 2/3 smaller that would get us through until we write web services that will do the job.

You can minimize the query length even more, IF you don't use any joins. This requires a bit of code manipulation in teh SqlServerSpecificCreator class in the SqlServer DQE code. If you change in there the Fieldname production to always be just the field, the query will be smaller.

Here is what we did:

public override void OnFetchTypedView(IRetrievalQuery selectQuery, IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill)
{
    string query = selectQuery.Command.CommandText;
    string field1, temp; int pos = 0;
    StringBuilder sb = new StringBuilder();
    
    sb.Append("SELECT ");

    pos = query.IndexOf("AS", 0);
    temp = query.Substring(7/*behind select!*/, pos - 7);

    pos = temp.LastIndexOf(".");
    field1 = temp.Substring(0, pos).Trim();

    sb.Append(field1);
    sb.Append(".* ");

    pos = query.IndexOf("FROM");
    sb.Append (query.Substring(pos));
            
    selectQuery.Command.CommandText = sb.ToString();
    base.OnFetchTypedView (selectQuery, fieldCollectionToFetch, dataTableToFill);
}

I replaced the column list with tableName.* in the overriden OnFetchTypedView and everything seems to work great! I did the same thing in the OnFetchEntityCollection and OnFetchEntity and it works. The queries are much much smaller than before.

Are there any side-effects that I should expect?

Not really, IF you don't use joins and with joins also some filters . So document this well, for the case that when you need a join, and it goes wrong somewhere, you know what's the cause

But it seems to work...

 exec sp_executesql N'SELECT [dbo].[rmtArtikli].* FROM  [dbo].[rmtStatusiArtikla] INNER JOIN [dbo].[rmtArtikli] ON  [dbo].[rmtStatusiArtikla].[Id]=[dbo].[rmtArtikli].[Sar_Id] WHERE ( UPPER([dbo].[rmtArtikli].[Naziv]) LIKE @Naziv1 And [dbo].[rmtStatusiArtikla].[Oznaka] <> @Oznaka2 And NOT [dbo].[rmtArtikli].[Id] IN (SELECT [dbo].[rmtGrupa_Artikl].[Art_Id] AS [Art_Id] FROM  [dbo].[optGrupe] INNER JOIN [dbo].[rmtGrupa_Artikl] ON  [dbo].[optGrupe].[Id]=[dbo].[rmtGrupa_Artikl].[Grp_Id] WHERE ( [dbo].[optGrupe].[Grr_Id] = @Grr_Id3))) ORDER BY [dbo].[rmtArtikli].[Naziv] ASC', N'@Naziv1 nvarchar(4),@Oznaka2 varchar(10),@Grr_Id3 decimal(15,0)', @Naziv1 = N'IBM%', @Oznaka2 = 'NKT', @Grr_Id3 = 11

That's if we don't need columns from the joined tables. But is there a way to do that? (select columns from joined tables in the entity). We also tested it with prefetchpaths with filters and sorters and it seems to work.

Only GetScalar gets the joined columns but this does not affect it.

Look at this creepy sql...

 exec sp_executesql N'SELECT [dbo].[dktVrstaDokumenta_Statusi].* FROM
[dbo].[dktVrstaDokumenta_Statusi] WHERE (
[dbo].[dktVrstaDokumenta_Statusi].[Id] IN (SELECT
[dbo].[dktDokument_Statusi].[Vds_Id] AS [Vds_Id] FROM
[dbo].[dktVrstaDokumenta_Statusi] INNER JOIN [dbo].[dktDokument_Statusi] ON
[dbo].[dktVrstaDokumenta_Statusi].[Id]=[dbo].[dktDokument_Statusi].[Vds_Id]
INNER JOIN [dbo].[dktVrsteDokumenata] ON
[dbo].[dktVrsteDokumenata].[Id]=[dbo].[dktVrstaDokumenta_Statusi].[Vdk_Id]
WHERE ( [dbo].[dktDokument_Statusi].[Dkm_Id] IN (SELECT
[dbo].[dktDokumenti].[Id] AS [Id] FROM  [dbo].[optOrganizacijskeJedinice]
INNER JOIN [dbo].[dktDokumenti] ON
[dbo].[optOrganizacijskeJedinice].[Id]=[dbo].[dktDokumenti].[Ojd_Id] INNER
JOIN [dbo].[dktDokument_Statusi] ON
[dbo].[dktDokumenti].[Id]=[dbo].[dktDokument_Statusi].[Dkm_Id] INNER JOIN
[dbo].[dktVrstaDokumenta_Statusi] ON
[dbo].[dktVrstaDokumenta_Statusi].[Id]=[dbo].[dktDokument_Statusi].[Vds_Id]
INNER JOIN [dbo].[dktVrsteDokumenata] ON
[dbo].[dktVrsteDokumenata].[Id]=[dbo].[dktDokumenti].[Vdk_Id] WHERE (
[dbo].[dktDokumenti].[Vdk_Id] = @Vdk_Id1 And
[dbo].[dktDokumenti].[PoslovnaGodina] = @PoslovnaGodina2 And
[dbo].[dktDokumenti].[Broj] = @Broj3 And
[dbo].[optOrganizacijskeJedinice].[Sifra] = @Sifra4 And
[dbo].[dktDokument_Statusi].[Vazeci] = @Vazeci5 And ( (
[dbo].[dktDokument_Statusi].[SpremanZaKnjizenje] = @SpremanZaKnjizenje6))
And [dbo].[dktDokument_Statusi].[DatumKnjizenja] IS NULL And ( (
[dbo].[dktVrstaDokumenta_Statusi].[Oznaka]IN (@Oznaka7, @Oznaka8) And
[dbo].[dktVrsteDokumenata].[Oznaka] <> @Oznaka9) Or (
[dbo].[dktVrstaDokumenta_Statusi].[Oznaka]IN (@Oznaka10, @Oznaka11) And
[dbo].[dktVrsteDokumenata].[Oznaka] = @Oznaka12)))) And ( (
[dbo].[dktDokument_Statusi].[Vazeci] = @Vazeci13 And ( (
[dbo].[dktDokument_Statusi].[SpremanZaKnjizenje] = @SpremanZaKnjizenje14))
And [dbo].[dktDokument_Statusi].[DatumKnjizenja] IS NULL And ( (
[dbo].[dktVrstaDokumenta_Statusi].[Oznaka]IN (@Oznaka15, @Oznaka16) And
[dbo].[dktVrsteDokumenata].[Oznaka] <> @Oznaka17) Or (
[dbo].[dktVrstaDokumenta_Statusi].[Oznaka]IN (@Oznaka18, @Oznaka19) And
[dbo].[dktVrsteDokumenata].[Oznaka] = @Oznaka20)))))))', N'@Vdk_Id1
decimal(15,0),@PoslovnaGodina2 decimal(4,0),@Broj3 int,@Sifra4 int,@Vazeci5
bit,@SpremanZaKnjizenje6 bit,@Oznaka7 varchar(10),@Oznaka8
varchar(10),@Oznaka9 varchar(10),@Oznaka10 varchar(10),@Oznaka11
varchar(10),@Oznaka12 varchar(10),@Vazeci13 bit,@SpremanZaKnjizenje14
bit,@Oznaka15 varchar(10),@Oznaka16 varchar(10),@Oznaka17
varchar(10),@Oznaka18 varchar(10),@Oznaka19 varchar(10),@Oznaka20
varchar(10)', @Vdk_Id1 = 11, @PoslovnaGodina2 = 2004, @Broj3 = 1, @Sifra4 =
201, @Vazeci5 = 1, @SpremanZaKnjizenje6 = 0, @Oznaka7 = 'ZKL', @Oznaka8 =
'STR', @Oznaka9 = 'MRC', @Oznaka10 = 'PLC', @Oznaka11 = 'STR', @Oznaka12 =
'MRC', @Vazeci13 = 1, @SpremanZaKnjizenje14 = 0, @Oznaka15 = 'ZKL',
@Oznaka16 = 'STR', @Oznaka17 = 'MRC', @Oznaka18 = 'PLC', @Oznaka19 = 'STR',
@Oznaka20 = 'MRC'

Scenario where this would not work:

SELECT table1., table2. FROM table1 inner join table2

and we need a column from table2... but that is not possible anyway (without views or typed list (we don't use typed lists anyway)).

Could you please explain the scenario where it wouldn't work in detail.

Thnx...

ivanc wrote:

Could you please explain the scenario where it wouldn't work in detail.

Thnx...

In 1.0.2004.2, I added multiple catalog support. In there, you can create 2 entities, which you can relate using a custom relation. Then create a typed list from them, and select fields from both.

Or create a dynamic list or typed list and grab a couple of fields from all the entities in the join list.

The code generating the SQL is generic for both typed lists and entity fetches. It's setup in such a way that it delegates the production of the various parts to the objects which contain the meta-data. This has a disadvantage which is that when you build up the select list of fields, you're not aware of joins in the from clause and aliasses stated there. 'best' would be to always alias tables, and use that alias in select lists, where clauses and group by clauses but at the moment they're not aware of each-other's existence so have to produce code on their own.

In theory, the specification of the full name of a field is better, as SqlServer then doesn't have to search for a field. (as I interpret the BOL documentation). In practise it can give some SQL which can be too verbose if you're on a thin line. In 1.0.2004.2, I've implemented catalog name/schema name overwriting in the config file which allows you to set the catalog name and the schema name to "", which effectively removes them from the query text (they're not emitted into the query) so you then get SELECT field1, Field2... FROM Table WHERE field...