LLBLGEN PRO 2.0 Adapter .Net 2005 Oracle 10g / SqlServer 2000

I am trying to improve performance of my project. It is working on both SqlServer 2000 and Oracle 10g. But there is a great performance difference between them Is there any performance difference between SqlServer and Oracle for llblgen. The difference is nearly 12 seconds. SqlServer is the slower one, although the machine configuration for the sqlServer is better.

Please check whether you need indexes defined in the sqlserver schema. Often this will help. Take one feature of your application at a time, so check which queries it produces, then define indexes for them.

If you're using compound PK's, check if you join with that table/entity on a subset of the fields and define a separate index for these subset. Example: northwind: order details and products. Orderdetails has a compound PK. Joining orderdetails and products won't utilize the index on the pk of orderdetails: you then should define a new index on productid of orderdetails.

We are using same indexes and design on SqlServer and Oracle. So it can not be about indexes and also there are not too many records in the tables of SqlServer. Count of records in oracle is nearly 5 times more than data in SqlServer.

We are using same indexes and design on SqlServer and Oracle. So it can not be about indexes

I don't think this is a given since each has it's own query optimization logic.

Indeed. And the optimizer of SqlServer is a bit better than the one on Oracle. It could be the oracle box has more memory, etc. set than the sqlserver box. The queries are alike, i.e.: the most optimal ones for the system as far as a SQL generator can construct them.

Are there particular queries which run slower on sqlserver or are all queries slower on sqlserver?

With any sort of performance issues you need to identify specific areas which are causing concerns and these areas should usually take the majority of the total time to complete the task e.g. one query on it's own shouldn't be reviewed and optimised however one query which take 25% of the time whereas another 10 take the remaining 75% probably should.

As Frans suggested I would compare specific queries or rather uses of LLBLGen code e.g. GetMulti on a collection or Save on an entity to see how long this takes for each database.

I'm sure there aren't intentionally any bottlenecks in LLBLGen providers for Oracle or SQL Server however it's always possible that the Oracle provider maybe able to be written more efficiently in some scenarios. If you spot one, perhaps as in this case, then you will need to be a lot more specific about where the differences lie so we can help diagnose this further.