what is the difference between ''ODBC and 'OLEDB' connection?which one we prefer?
thans in advance
i attached some interesting link and a image to undestand the difference
* If the user wants to access data independent of the SQL language , you should migrate to OLE DB . As noted , the connectors are linked to ODBC SQL language.
* If the user's problem can solve directly manipulating tables , it is best to use OLE DB .
* If the user wishes to multidimensional data parallel interfaces , OLE DB is recommended as it is the only standard for data and models with these characteristics, and reduces the required amount of code and execution time.
* If the user requires access to different databases (local, shared , IOM, etc. ) from a single application , use OLE DB , otherwise you will be using separate routes for each access method code. This is also based on the idea that the OLE DB connectors reduce the differences between drivers , SQL and DBMS dialects have been problems between the ODBC settings.
* If the user need is to run concurrent updates , the OLE DB architecture is the best choice . To update records using an ODBC driver , the user must generate SQL UPDATE statements that are basically oriented batch updates . There is no concept of records in an ODBC block model.
The OLE DB architecture accommodates various models allowing the management of data from many more applications.
It is for this that QlikView for developing complex models that depend on multiple databases , you should use OLE DB connections instead of ODBC connections.
At Qlikview we can use both ODBC or OLEDB, basically the ODBC driver DataSources are used to OLEDB drivers to access Data. Also OLEDB can access XML Files, Directory services and Exchange Servers too.
In terms of performance, any database layer will increase access time at least 40% in comparative with native access.
At Qlikview we made a two or three steps architecture to gain performance. Just Like any ETL process, extract all the data from your database and store it in QVD files, after that, Transform the data from QVD to your application. Qlikview QVD files are extremmally optimized to gain performance,
If you use SQL Server, consider ODBC for future compatibility. Though Microsoft was the architect of the OLE DB standard, they're moving on.
All of the above is true: OLE DB is more modern and has a richer interface.
However, it very rarely matters which you choose. Bot work fine, and modern ODBC drivers are often just as fast as OLE DB. Pick one connection type that works, and don't spend time on optimizing something that doesn't matter.