none
how to write cursor alternative query in sql server RRS feed

  • Question



  • Hi ,I need to implement without using cursor logice.below implementaion script using cursor its taking 5 hours time for 1.4 lakhs records.
    please tell me any alternative solution to resolve performance issue in sql server 

    below  temp table I have provided few sample records. in the orginal table have 1 lakh records above.
    SET NOCOUNT ON

    CREATE TABLE #temp (
    RecordID int identity,
    Address varchar(50),
    City varchar(30),
    State varchar(5),
    GPSLat numeric(9,6),
    GPSLong numeric(9,6),
    MapURL varchar(255))

    INSERT INTO #temp (Address, City, State)
    VALUES ('1033 Southwest 152nd Street', 'Burien', 'WA')

    INSERT INTO #temp (Address, City, State)
    VALUES ('11910 Northeast 154th Street', 'Brush Prairie', 'WA')

    INSERT INTO #temp (Address, City, State)
    VALUES ('500 SeaWorld Drive', 'San Diego', 'CA')

    INSERT INTO #temp (Address, City, State)
    VALUES ('1 Legoland Drive', 'Carlsbad', 'CA')


    DECLARE curGeo CURSOR LOCAL STATIC FOR
    SELECT RecordID, Address, City, State
    FROM #temp

    DECLARE @RecordID int
    DECLARE @Address varchar(50)
    DECLARE @City varchar(30)
    DECLARE @State varchar(5)
    DECLARE @GPSLatitude numeric(9, 6)
    DECLARE @GPSLongitude numeric(9, 6)
    DECLARE @MapURL varchar(255)

    OPEN curGeo

    FETCH curGeo INTO
    @RecordID,
    @Address,
    @City,
    @State

    WHILE @@FETCH_STATUS = 0 BEGIN

    BEGIN TRY
    EXEC opsstream.sputilGeocode
    @Address = @Address OUTPUT,
    @City = @City OUTPUT,
    @State = @State OUTPUT,
    @GPSLatitude = @GPSLatitude OUTPUT,
    @GPSLongitude = @GPSLongitude OUTPUT,
    @MapURL = @MapURL OUTPUT

    UPDATE #temp
    SET
    GPSLat = @GPSLatitude,
    GPSLong = @GPSLongitude,
    MapURL = @MapURL
    WHERE
    RecordID = @RecordID

    END TRY
    BEGIN CATCH
    PRINT 'Warning: RecordID ' + CAST(@RecordID AS varchar(100)) + ' could not be geocoded.' 
    END CATCH


    FETCH curGeo INTO
    @RecordID,
    @Address,
    @City,
    @State
    END

    SELECT * FROM #temp
    please tell me how to aporach to avoid cursor and give good performance.please tell me how to write query  to achive this task in sql server.
    Sunday, August 20, 2017 4:42 AM

All replies

  • If that temp table appears in the actual solution, I see a direct option for performance improvement: add a primary key constraint on RecordID.

    Ideally, you would have to rewrite opsstream.sputilGeocode to work with table input and then work with set-based operations on that table. Depending on what's in it, it can be a quick affair, or it can so much work that it is not a defensible investment. However, it may still be possible to improve the performance of the procedure, so that the execution time for the milliow-row long loop is reduced. There can things like small temp tables without indexes, which we have already seen one example of. When you do things like that 1.4 million times, it adds up.

    Sunday, August 20, 2017 9:43 AM
  • >> I need to implement without using cursor logic. You will below implementation script using cursor its taking 5 hours time for 140000 records [sic]. <<

    Going back to the basics, rows are nothing like records. Your mindset is not in RDBMS.

    It would also be nice if you posted numbers in the same format used in SQL instead of some local dialect. The term milliard can also be used to refer to 1,000,000,000; whereas "milliard" is valid but seldom used in English, variations on this name often appear in other languages. In the South Asian numbering system, it is known as 100 crore or 1 Arab. This is why IT people have universal standards, not local dialects. 

    You also failed to follow ISO 11179 naming rules. Think about it! What does “city” mean? Population? I’m going to guess from the sample data . You gave you wanted the name of the city. Those postfixes on a valid data element name are called attribute properties. Get any book on data modeling or (if you’re really into a lot of pain) read the original metadata committee papers. If you would done a valid model. Would it look like this?:

    CREATE TABLE Locations
    (gps_lat NUMERIC(9,6) NOT NULL,
     gps_long NUMERIC(9,6) NOT NULL,
     PRIMARY KEY (gps_lat, gps_long)
     street_address VARCHAR(35) NOT NULL,
     city_name VARCHAR(20) NOT NULL,
     state_code CHAR(2) NOT NULL
     CHECK(state_code IN (..)),
    map_url VARCHAR(255) NOT NULL);

    I am assuming that you will not have problems with Byzantium, Constantinople and Istanbul in your data model.

    Did you just stupid that the use of the insertion order of physical records into a table on one machine, what Sybase called the IDENTITY table property (no, it’s not even a real column!) can never be a valid relational key? This topic is usually covered in the first two weeks of any decent class on RDBMS.

    >> please tell me how to approach to avoid cursor and give good performance. Please tell me how to write query to archive this task in SQL server. <<

    go to the Internet and find a data source that will have all this information (except for probably the map URL) as a free download.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, August 20, 2017 10:00 PM