Post reply

OPENROWSET query tying up remote database

  • April 28, 2009 at 10:09 am

    #129740

    I run an openrowset query against a remote database that is actively performing inserts and updates at the time I am requesting data and the remote database seems to go into a busy wait causing command timeouts. Not sure why a select using openrowset would interfere with the remote servers local activities so dramatically.

    Are there any additional parameters I need to include in the openrowset command string to reduce the priority of the request so it does not interfere with the processes actively running on the remote database?

  • April 30, 2009 at 7:20 am

    #986638

    Your select will probably be taking Shared locks on the data as it reads it in. Shared locks are incompatible with the update and exclusive locks that the updates need to acquire. You should consider using a linked server instead, calling a procedure rather than using openrowset, and either reading potentially dirty data by operating at the READ UNCOMMITTED isolation level, or you could enable one of the row level versioning isolation levels. The better solution depends on your exact requirements.

    Note that posts with example code, lots of detail, and evidence of things you have tried for yourself, will tend to get better and faster replies.

    Paul

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • April 30, 2009 at 9:58 am

    #986740

    This is excellent feedback and may provide me with a direction to look in. I was using a link server for this operation and had two problems.

    1 - I could not dynamically build a connection string in my c# program and pass it to my SQL request to toggle the data source dynamically without jumping through some pretty ugly hoops.

    2 - The link server took 20 minutes to process this request which I tracked down to logging and general overhead associated with the link server and the OPENROWSET only took 2 minutes to perform the exact same request.

    Let me know if you have any other thoughts. I will keep looking.

  • April 30, 2009 at 2:56 pm

    #986986

    Do you recall I said something about example code...? 😉

    Paul

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • April 30, 2009 at 3:08 pm

    #986997

    Hi

    tschilling (4/30/2009)

    1 - I could not dynamically build a connection string in my c# program and pass it to my SQL request to toggle the data source dynamically without jumping through some pretty ugly hoops.

    How many different databases with same type do you have? Isn't it possible to create a permanent link?

    2 - The link server took 20 minutes to process this request which I tracked down to logging and general overhead associated with the link server and the OPENROWSET only took 2 minutes to perform the exact same request.

    You should have a look to OPENQUERY instead of direct JOINing your tables with the linked server. This will execute the remote request directly on remote server and should affect a better performance.

    Greets

    Flo

    Off Topic: Good morning Paul! 🙂

  • April 30, 2009 at 3:22 pm

    #987005

    Morning Flo!

    It is a lovely sunny and crisp day in Wellington. The sunlight is sparkling off the sea in the harbour, and all is good with the world.

    I hope things are equally good with you.

    I'm looking forward to seeing the code example for this one 😉

    Paul

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • April 30, 2009 at 3:31 pm

    #987015

    Paul White (4/30/2009)

    It is a lovely sunny and crisp day in Wellington. The sunlight is sparkling off the sea in the harbour, and all is good with the world.

    I hope things are equally good with you.

    Pleased to hear that you have a great start into your day! Harbour, sea, ... seems, I'm in the wrong country 😉

    Today was probably the worst of the last two month at work (escalations, rollback, two hot fixes). Anyway, I feel good and it doesn't matter!! Tomorrow is holiday here and it will be great weather 😀

    Best wishes

    Flo

  • April 30, 2009 at 3:54 pm

    #987038

    Sunny and beautiful here in Portland, OR!

    As for the number of the number of databases, 20 for now sprinkled all over the country in veneer mills with really small network pipes. Databases are being added and removed routinely and in some cases, computers are being swapped out and the network ops guy's giving them new names which really complicates things. Right now I have it so the active database list is maintained via the c# application that runs all things related to this manufacturing process. This part works well and the users are very happy with the interface.

    As for code, here is one of the main stored proc's -

    -- exec MarkingDB.[dbo].copyversiontohost 102,'01','Server=PDXXPD-02\SQLEXPRESS;uid=MarkDBUser;pwd=MarkDBUser'

    ALTER Proc [dbo].[copyversiontohost] (@TargetVerKey int,

    @TargetFactory nvarchar(2),

    @DatabaseStr nvarchar(100))

    as

    declare @SelectStr nvarchar(2000)

    declare @ExecStr nvarchar(2000)

    /* Remove this for now as we will be loading multiple versions of which only one can be the default.

    Change the default version number using the marking lines maitenace function.

    Update [MarkingLines] SetVerKey = @TargetVerKey

    where MarkingLine = @MarkingLine

    andFactory = @TargetFactory

    */

    If Not Exists (Select VerKey

    From [dbo].[VersionControl]

    where VerKey = @TargetVerKey )

    begin

    set @SelectStr = N'SELECT [Description],[StatusFlag],[CreateDate],[ModifiedDate],[VersionNotes] from [MarkingDB].[dbo].[VersionControl] where VerKey = ' + convert(varchar(8),@TargetVerKey)

    set @ExecStr = N'select ' + convert(nvarchar(8),@TargetVerKey) + N', ''' + @TargetFactory + N''', a.* FROM OPENROWSET(''SQLNCLI'', ''' + @DatabaseStr + ''', ''' + @SelectStr + N''') AS a'

    Insert [dbo].[VersionControl] ([VerKey]

    ,[Factory]

    ,[Description]

    ,[StatusFlag]

    ,[CreateDate]

    ,[ModifiedDate]

    ,[VersionNotes])

    exec sp_executesql @ExecStr

    end

    Delete [dbo].[CompValues] where VerKey = @TargetVerKey

    Delete [dbo].[CombHigherGrade] where VerKey = @TargetVerKey

    Delete [dbo].[CombineDrops] where VerKey = @TargetVerKey

    Delete [dbo].[CompSizeVers] where VerKey = @TargetVerKey

    Delete [dbo].[MajorGrades] where VerKey = @TargetVerKey

    Delete [dbo].[GradeLink] where VerKey = @TargetVerKey

    Delete [dbo].[GradeControls] where VerKey = @TargetVerKey

    Delete [dbo].[SpeciesControls] where VerKey = @TargetVerKey

    Delete [dbo].[FactoryControls] where VerKey = @TargetVerKey

    Delete [dbo].[RMSpeciesControls] where VerKey = @TargetVerKey

    Delete [dbo].[RMCompValues] where VerKey = @TargetVerKey

    Delete [dbo].[RMValueExceptions] where VerKey = @TargetVerKey

    -- Factory Controls

    begin

    set @SelectStr = N'SELECT ChuteSize,AltChuteSize,MaxTrim,ExcessClip From [MarkingDB].[dbo].FactoryControls where VerKey = ' + convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' + convert(nvarchar(8),@TargetVerKey) + N', ''' + @TargetFactory + N''', a.* FROM OPENROWSET(''SQLNCLI'', ''' + @DatabaseStr + ''', ''' + @SelectStr + N''') AS a where ' + convert(nvarchar(8),@TargetVerKey) + ' not in (Select VerKey from [dbo].[FactoryControls])'

    Insert into [dbo].[FactoryControls] (VerKey,

    Factory,

    ChuteSize,

    AltChuteSize,

    MaxTrim,

    ExcessClip)

    exec sp_executesql @ExecStr

    end

    -- Species Controls

    set @SelectStr = N'SELECT [MarkIndex],[MinSaverSize],[MinCompSize],[SaverFlag],[PiecesPerBook],[SmallestBackSize] from [MarkingDB].[dbo].SpeciesControls where VerKey = ' + convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' + convert(nvarchar(8),@TargetVerKey) + N', a.* FROM OPENROWSET(''SQLNCLI'', ''' + @DatabaseStr + ''', ''' + @SelectStr + N''') AS a where ' + convert(nvarchar(8),@TargetVerKey) + ' not in (Select VerKey from [dbo].SpeciesControls)'

    Insert [dbo].[SpeciesControls] ([VerKey]

    ,[MarkIndex]

    ,[MinSaverSize]

    ,[MinCompSize]

    ,[SaverFlag]

    ,[PiecesPerBook]

    ,[SmallestBackSize])

    exec sp_executesql @ExecStr

    -- Grade Controls

    set @SelectStr = N'SELECT [MarkIndex],[MarkGrade],[OddPieceGrade],[MinCompSize],[GradeRank] from [MarkingDB].[dbo].[GradeControls] where VerKey = ' + convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' + convert(nvarchar(8),@TargetVerKey) + N', a.* FROM OPENROWSET(''SQLNCLI'', ''' + @DatabaseStr + ''', ''' + @SelectStr + N''') AS a where ' + convert(nvarchar(8),@TargetVerKey) + ' not in (Select VerKey from [dbo].[GradeControls])'

    Insert [dbo].[GradeControls] ([VerKey]

    ,[MarkIndex]

    ,[MarkGrade]

    ,[OddPieceGrade]

    ,[MinCompSize]

    ,[GradeRank])

    exec sp_executesql @ExecStr

    -- Grade Link

    set @SelectStr = N'SELECT [MarkIndex],[FromGrade],[ToGrade],[ResultGrade] from [MarkingDB].[dbo].[GradeLink] where VerKey = ' + convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' + convert(nvarchar(8),@TargetVerKey) + N', a.* FROM OPENROWSET(''SQLNCLI'', ''' + @DatabaseStr + ''', ''' + @SelectStr + N''') AS a where ' + convert(nvarchar(8),@TargetVerKey) + ' not in (Select VerKey from [dbo].[GradeLink])'

    Insert [dbo].[GradeLink] ([VerKey]

    ,[MarkIndex]

    ,[FromGrade]

    ,[ToGrade]

    ,[ResultGrade])

    exec sp_executesql @ExecStr

    -- Combine Higher Grade

    set @SelectStr = N'SELECT [MarkIndex],[MarkGrade],[CombPercent] from [MarkingDB].[dbo].[CombHigherGrade] where VerKey = ' + convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' + convert(nvarchar(8),@TargetVerKey) + N', a.* FROM OPENROWSET(''SQLNCLI'', ''' + @DatabaseStr + ''', ''' + @SelectStr + N''') AS a where ' + convert(nvarchar(8),@TargetVerKey) + ' not in (Select VerKey from [dbo].[CombHigherGrade])'

    Insert [dbo].[CombHigherGrade] ([VerKey]

    ,[MarkIndex]

    ,[MarkGrade]

    ,[CombPercent])

    exec sp_executesql @ExecStr

    -- Combine Drops

    set @SelectStr = N'SELECT [MarkIndex],[FirstGrd],[SecondGrd],[DropRule] from [MarkingDB].[dbo].[CombineDrops] where VerKey = ' + convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' + convert(nvarchar(8),@TargetVerKey) + N', a.* FROM OPENROWSET(''SQLNCLI'', ''' + @DatabaseStr + ''', ''' + @SelectStr + N''') AS a where ' + convert(nvarchar(8),@TargetVerKey) + ' not in (Select VerKey from [dbo].[CombineDrops])'

    Insert [dbo].[CombineDrops] ([VerKey]

    ,[MarkIndex]

    ,[FirstGrd]

    ,[SecondGrd]

    ,[DropRule])

    exec sp_executesql @ExecStr

    -- Component Size Versions

    set @SelectStr = N'SELECT [MarkIndex] ,[CompCode],[CompSize] from [MarkingDB].[dbo].[CompSizeVers] where VerKey = ' + convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' + convert(nvarchar(8),@TargetVerKey) + N', a.* FROM OPENROWSET(''SQLNCLI'', ''' + @DatabaseStr + ''', ''' + @SelectStr + N''') AS a where ' + convert(nvarchar(8),@TargetVerKey) + ' not in (Select VerKey from [dbo].[CompSizeVers])'

    Insert [dbo].[CompSizeVers] ([VerKey]

    ,[MarkIndex]

    ,[CompCode]

    ,[CompSize])

    exec sp_executesql @ExecStr

    -- Component Values

    set @SelectStr = N'SELECT [MarkIndex] ,[MarkGrade] ,[CompCode] ,[CompValue] from [MarkingDB].[dbo].[CompValues] where VerKey = ' + convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' + convert(nvarchar(8),@TargetVerKey) + N', a.* FROM OPENROWSET(''SQLNCLI'', ''' + @DatabaseStr + ''', ''' + @SelectStr + N''') AS a where ' + convert(nvarchar(8),@TargetVerKey) + ' not in (Select VerKey from [dbo].[CompValues])'

    Insert [dbo].[CompValues] ([VerKey]

    ,[MarkIndex]

    ,[MarkGrade]

    ,[CompCode]

    ,[CompValue])

    exec sp_executesql @ExecStr

    -- Major Grades

    set @SelectStr = N'SELECT [MarkIndex] ,[MajorGrade] from [MarkingDB].[dbo].[MajorGrades] where VerKey = ' + convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' + convert(nvarchar(8),@TargetVerKey) + N', a.* FROM OPENROWSET(''SQLNCLI'', ''' + @DatabaseStr + ''', ''' + @SelectStr + N''') AS a where ' + convert(nvarchar(8),@TargetVerKey) + ' not in (Select VerKey from [dbo].[MajorGrades])'

    Insert [dbo].[MajorGrades] ([VerKey]

    ,[MarkIndex]

    ,[MajorGrade])

    exec sp_executesql @ExecStr

    -- Running match section

    -- RM Factory Controls

    set @SelectStr = N'SELECT [Factory], [CompLoss], [STDPanelWidth], [STDRecovery] from [MarkingDB].[dbo].[RMFactoryControls] where VerKey = ' +

    convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' +

    convert(nvarchar(8),@TargetVerKey) +

    N', a.* FROM OPENROWSET(''SQLNCLI'', ''' +

    @DatabaseStr + ''', ''' +

    @SelectStr + N''') AS a where ' +

    convert(nvarchar(8),@TargetVerKey) +

    ' not in (Select VerKey from [dbo].[RMFactoryControls])'

    Insert [dbo].[RMFactoryControls] ([VerKey],

    [Factory],

    [CompLoss],

    [STDPanelWidth],

    [STDRecovery])

    exec sp_executesql @ExecStr

    -- RM Species Controls

    set @SelectStr = N'SELECT [MarkIndex], [M50HighGrade], [MaxCompHigh], [MaxCompLow] from [MarkingDB].[dbo].[RMSpeciesControls] where VerKey = ' +

    convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' +

    convert(nvarchar(8),@TargetVerKey) +

    N', a.* FROM OPENROWSET(''SQLNCLI'', ''' +

    @DatabaseStr + ''', ''' +

    @SelectStr + N''') AS a where ' +

    convert(nvarchar(8),@TargetVerKey) +

    ' not in (Select VerKey from [dbo].[RMSpeciesControls])'

    Insert [dbo].[RMSpeciesControls] ([VerKey]

    ,[MarkIndex]

    ,[M50HighGrade]

    ,[MaxCompHigh]

    ,[MaxCompLow])

    exec sp_executesql @ExecStr

    -- RM Component Values

    set @SelectStr = N'SELECT [MarkIndex], [MarkGrade], [GrossSalesValue], [DownGradePercent], [SplCostPerMSF], [AveNumComps] from [MarkingDB].[dbo].[RMCompValues] where VerKey = ' +

    convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' +

    convert(nvarchar(8),@TargetVerKey) +

    N', a.* FROM OPENROWSET(''SQLNCLI'', ''' +

    @DatabaseStr + ''', ''' +

    @SelectStr + N''') AS a where ' +

    convert(nvarchar(8),@TargetVerKey) +

    ' not in (Select VerKey from [dbo].[RMCompValues])'

    Insert [dbo].[RMCompValues] ([VerKey]

    ,[MarkIndex]

    ,[MarkGrade]

    ,[GrossSalesValue]

    ,[DownGradePercent]

    ,[SplCostPerMSF]

    ,[AveNumComps])

    exec sp_executesql @ExecStr

    -- RM Value Exceptions

    set @SelectStr = N'SELECT [MarkIndex], [FromGrade], [ExceptionSize], [ToGrade] from [MarkingDB].[dbo].[RMValueExceptions] where VerKey = ' +

    convert(nvarchar(8),@TargetVerKey)

    select @ExecStr = N'select ' +

    convert(nvarchar(8),@TargetVerKey) +

    N', a.* FROM OPENROWSET(''SQLNCLI'', ''' +

    @DatabaseStr + ''', ''' +

    @SelectStr + N''') AS a where ' +

    convert(nvarchar(8),@TargetVerKey) +

    ' not in (Select VerKey from [dbo].[RMValueExceptions])'

    Insert [dbo].[RMValueExceptions] ([VerKey]

    ,[MarkIndex]

    ,[FromGrade]

    ,[ExceptionSize]

    ,[ToGrade])

    exec sp_executesql @ExecStr

  • April 30, 2009 at 4:10 pm

    #987049

    I know, some people here will beat me for this, though...

    Since your procedure looks more like a export-import tool and you have already a C# application. Why don't you connect directly to your remote servers from your client using a SqlDataReader to import into your database?

    I think the source code would be much more simple in C# than in SQL and you have the control to your SqlConnection and commands and their timeouts.

    Yet another approach:

    What about a SSIS job?

    Greets

    Flo

  • April 30, 2009 at 4:12 pm

    #987050

    Sorry, forgot:

    For future posts it would be great if you encapsulate your source code into code tags:

    [ code ]

    YOUR SQL

    [ /code ]

    ... without the spaces.

    This makes it much more readable :-). Maybe just edit your previous post and put the tags around your code. Usually this causes much more people to read it.

    Edited: Thanks!

    Greets

    Flo

  • April 30, 2009 at 4:19 pm

    #987054

    This is my first post so I appreaciate your paitence. I edited the post and now know to look at the available tags to make it a more humane experience.

    Thank you both for the leg up!

  • April 30, 2009 at 4:23 pm

    #987056

    I saw that you are new here so you are very welcome 🙂

    Did you consider my other suggestion?

  • April 30, 2009 at 4:27 pm

    #987060

    You could have a SQL Agent job that goes around creating the appropriate linked servers for you, I have seen that approach taken before.

    I tend to agree with Flo though - if it is possible to do it from the application, that might be the better option. There's little point redirecting the data through an intermediate server, if your requirements allow for the data to be fetched directly.

    As usual: it depends 🙂

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • April 30, 2009 at 4:45 pm

    #987074

    You feedback is excellent and I have done some of what your suggesting but I will need to take another look it with fresh eyes'.

    I am now wondering if a better approach would be to look at the update routine to see if I can do something with that to limit resource conflicts.

    The following stored proc gets executed in a background thread via a SQL command queue. There are anywhere from 1 to 18 rows of data that are looped through executing this code. Where I am running into problems is when I try to pull data from the same database and table that this proc is being executed on.

    Currently, I am not associating a SQL transaction with this update so I am wonder if I add a transaction, will that isolate the data being written at the time the query is reading the table preventing conflict?

    ALTER proc [dbo].[updtmarkdata] (@WorkStationID nchar (10),

    @VerKey int,

    @MarkIndex int,

    @MarkingLine nvarchar (3),

    @Shift nvarchar (2),

    @Operator nvarchar (3),

    @BookNumber int,

    @ShiftDate datetime,

    @BookDate nvarchar(25),

    @PiecesPerBook int,

    @NumCalledGrades int,

    @NumGradesAssigned int,

    @ItemCount int,

    @CalledGrade nvarchar (3),

    @CalledInches decimal (18,2),

    @MarkGrade nvarchar (3),

    @CompCode int,

    @ClipSize decimal (18,2),

    @MarkMethod nvarchar(5),

    @MatchType varchar(10),

    @FlagBook bit,

    @DataReplicated bit)

    as

    If Not Exists (Select dbo.MarkData.VerKey, dbo.MarkData.MarkIndex, dbo.MarkData.MarkingLine, dbo.MarkData.Shift, dbo.MarkData.Operator,

    dbo.MarkData.BookNumber, dbo.MarkData.ShiftDate, dbo.MarkData.BookDate, dbo.MarkData.PiecesPerBook, dbo.MarkData.NumCalledGrades,

    dbo.MarkData.NumGradesAssigned, dbo.MarkData.ItemCount, dbo.MarkData.CalledGrade,

    dbo.MarkData.CalledInches, dbo.MarkData.MarkGrade, dbo.MarkData.CompCode, dbo.MarkData.ClipSize, dbo.MarkData.MarkMethod, dbo.MarkData.MType

    From dbo.MarkData

    where dbo.MarkData.WorkStationID = @WorkStationID

    and dbo.MarkData.VerKey = @VerKey

    anddbo.MarkData.MarkIndex = @MarkIndex

    and dbo.MarkData.MarkingLine = @MarkingLine

    and dbo.MarkData.Shift = @Shift

    and dbo.MarkData.Operator = @Operator

    and dbo.MarkData.BookNumber = @BookNumber

    and dbo.MarkData.ShiftDate = @ShiftDate

    and dbo.MarkData.BookDate = @BookDate

    and dbo.MarkData.PiecesPerBook = @PiecesPerBook

    anddbo.MarkData.NumCalledGrades = @NumCalledGrades

    and dbo.MarkData.NumGradesAssigned = @NumGradesAssigned

    and dbo.MarkData.ItemCount = @ItemCount

    and dbo.MarkData.CalledGrade = @CalledGrade

    and dbo.MarkData.CalledInches = @CalledInches

    and dbo.MarkData.MarkGrade = @MarkGrade

    and dbo.MarkData.CompCode = @CompCode

    and dbo.MarkData.ClipSize = @ClipSize

    and dbo.MarkData.MarkMethod = @MarkMethod

    and dbo.MarkData.MType = @MatchType)

    begin

    Insert into MarkData (WorkStationID,

    VerKey,

    MarkIndex,

    MarkingLine,

    Shift,

    Operator,

    BookNumber,

    ShiftDate,

    BookDate,

    PiecesPerBook,

    NumCalledGrades,

    NumGradesAssigned,

    ItemCount,

    CalledGrade,

    CalledInches,

    MarkGrade,

    CompCode,

    ClipSize,

    MarkMethod,

    MType,

    FlagBook,

    DataReplicated)

    Values(@WorkStationID, @VerKey, @MarkIndex, @MarkingLine, @Shift, @Operator, @BookNumber,

    @ShiftDate, @BookDate, @PiecesPerBook, @NumCalledGrades,

    @NumGradesAssigned, @ItemCount, @CalledGrade, @CalledInches,

    @MarkGrade, @CompCode, @ClipSize, @MarkMethod, @MatchType, @FlagBook, @DataReplicated)

    end

  • April 30, 2009 at 7:23 pm

    #987109

    Consider makling the INSERT and EXISTS test the same statement:

    INSERT dbo.Table (...columns..)

    SELECT @primary_key_value, ...other columns

    WHERE NOT EXISTS

    (

    SELECT 1 FROM dbo.Table

    WHERE dbo.Table.primary_key_column = @primary_key_value

    )

    Make sure you have a primary key on that table.

    Paul

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy