i trying understand sql server behaviour in below case.
i fetching around half million rows oracle sql server 2005 database using linked server created in sql server. using around 50 odd columns insert select query.
i found query did not finish , used run indefinitly until stop manually. after around 1 hr if check destination table able find rows in table. neither did query stop executing nor did release exclusive lock on desination table.
when fired simple query oracle linked server, figured out of datatypes oracle , sql server had explicitly converted (esp oracle numeric sql server varchar).
after datatypes had issue explicitly converted, able finish entire fetch without problems i.e. query stops , releases exculsive lock on table.
the question have is, why did query fetch data despite datatype conversion issues in query , why did held lock after fetch data? explanation higly appreciated (even though problem resolved :) )
ms sql uses various locks, row, column, table. escalated lock table. update each relevant index on each row insert unless tell not to. there 2 ways around this: insert rows in batches: 1000 or 10000, experiment find batch size columns width. or, disable indexes (and remember renable them). mssql have type conversions in 1 entire select , use tempdb or memory if there limits there, slow things down: memory swap file etc, doing in batches elliminate bottleneck. each insert implicit transation, consistency sql needs able roll back... not forget writng log files.
Comments
Post a Comment