Hello

I noticed this tonight while running SQL Profiler to assist a co-worker with a performance issue. It looks like the web application is always connecting to “DB1” and the service application code is always connecting to the “master” database. Then many of the queries are being done remotely. For example, all queries to DB1 are remote. If your database happens to be named something other than DB1 (using the .config file to change the catalog name) then all queries are being performed remotely.

SQL Server does a lot better than it used to with remote queries. They used to not be optimized at all. Still, they’re in a different category than local queries and generally will be less optimized and run slower.

It would be better if a connection was made to the database actually being used and, that multiple connections are used if we’re connecting to separate databases. I don’t know for sure, I have yet to dig into the LLBLGen Pro classes, but my guess is this is a configuration issue. Please if it is could you point me in the right direction?

Thank you, Bruno Valle

an action can be executed over a single connection. So if you're fetching a set of entities, that's a single connection. Per action, llblgen pro opens and closes a new connection. If you're using adapter, you can override the CreateNewPhysicalConnection method in a derived class of DataAccessAdapter and in there pass in a different connection string.

Or, you can pass in a different connection string when you create the adapter. However, it's not as if you can say: "these tables are on this server and these tables are on that server" with an easy configuration setting.

Perhaps I was not clear on my current configuration.

I do not mix databases on the LLBLGen Pro project, so I have one project for DB1 and another for DB2, however both of them use the same .config file. Since both DB1 and DB2 are on the same server. This way all the tables in one project belong only to one database. We did this way because table names might conflict between databases.

The question would be when the default database when the LLBLGen Pro project was DB1 and we use DB1 that is all fine, but when we change, using the .config file, for DB1 to be called DB1_CustomerOne, then it seems all queries are been done remotely. Is this normal behavior? Can it be fixed?

Please let me know.

Thank you, Bruno Valle

The question would be when the default database when the LLBLGen Pro project was DB1 and we use DB1 that is all fine, but when we change, using the .config file, for DB1 to be called DB1_CustomerOne, then it seems all queries are been done remotely. Is this normal behavior? Can it be fixed?

Have you changed the database name in the connection string which is found in the config file? Or you have only used Catalog name overwriting ?

Also, how can llblgen pro determine if it should still use DB1 for table X and DB1_customerone for table Y?

You can redirect references to databases or schemas by specifying name overwrites. If you need to access DB2, simply specify the name overwrite. Though that of course is valid for all references, so all DB1 references are redirected.

If you want to specify that per call, you can, in adapter. It's not that straight forward so I would definitely suggest to you to group the tables together in catalogs so they are threated like a unit: e.g. all tables shared for all customers in catalog A, and all customer specific tables in catalog B. You then can redirect per customer references to B to the specific customer catalog.

Please check: Using the generated code -> Adapter -> DataAccessAdapter functionality. You then can, per call, redirect references.

Have you changed the database name in the connection string which is found in the config file? Or you have only used Catalog name overwriting ?

Catalog name overwriting

Also try to set the database name in the connection string.

Would you also please address my questions I posted above? It's confusing to me what tables in which catalog need to be redirected and if there should be tables in original catalog be left alone...