Can Sql Server do this?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Can Sql Server do this?

  1. #1
    DavidR Guest

    Can Sql Server do this?


    Question from a SAS programmer:

    I don't have much experience with SQL Server, but have used the native SQL
    code to create datasets from DB2 tables.

    Here where I work, we use SAS primarily to do data processing - for example,
    a database of customer records that consists of an updated record for each
    customer at the end of each month - with the same variables (fields) such
    as the balance.

    We routinely take recordsets like this and transform them into one record
    for each with a renamed field for each previous duplicate field - balance_03_2001
    for example.

    We're trying to find alternatives to SAS. Is this something that SQL Server
    can do efficiently? Are there better alternatives? What would the solution
    to this problem look like?

    Thanks,

    David Ramsey

  2. #2
    david Guest

    Re: Can Sql Server do this?


    Hi David,

    I don't know anything about SAS but if this might be of any help to you,
    I would like to say that SQL Server, like any other Relational Database Management
    System (RDBMS), suits most of the business information scenarios we might
    think of and would therefore be much appropriate for your requirements too.
    If you ever switch to SQL Server, you'll most certainly benefit from a wide
    variety of features that would make your life easier. You would have Stored
    Procedures, Views and Triggers amongst others to help you efficiently organise
    and process your data and bcp features (for fast bulk copying data to and
    from text files) as well as basic backup tools for your archiving needs.
    I do certainly believe SQL Server is a valuable option for your business
    requirements for the features, reliability and performance you might obviously
    need. You can find more info in the Microsoft site at http://www.microsoft.com.
    I develop solutions using VB 5.0 / SQL Server 6.5 / MS Access 97 and if you
    think there are areas in which i might help you, please do feel free to mail
    me.

    Regards,

    David
    david@servihoo.com


    "DavidR" <david.c.ramsey@bankofamerica.com> wrote:
    >
    >Question from a SAS programmer:
    >
    >I don't have much experience with SQL Server, but have used the native SQL
    >code to create datasets from DB2 tables.
    >
    >Here where I work, we use SAS primarily to do data processing - for example,
    >a database of customer records that consists of an updated record for each
    >customer at the end of each month - with the same variables (fields) such
    >as the balance.
    >
    >We routinely take recordsets like this and transform them into one record
    >for each with a renamed field for each previous duplicate field - balance_03_2001
    >for example.
    >
    >We're trying to find alternatives to SAS. Is this something that SQL Server
    >can do efficiently? Are there better alternatives? What would the solution
    >to this problem look like?
    >
    >Thanks,
    >
    >David Ramsey



  3. #3
    DavidR Guest

    Re: Can Sql Server do this?


    david,

    Thanks so much for the response - here's a scenario type question for you:

    I currently have a database that has upwards of 7 million records. MS Access,
    as you can well imagine, is seriously slow in doing anything with this large
    amount of data. I use Access to link to some RDBMS tables on another system,
    and after pulling in the data that I need, the Access file can exceed 800
    MB easily. Of course simply performing a sort on that amount of data can
    take 30 min. to an hour (typically).

    This is why most of the processing to rearrange the data is done remotely
    on a Unix based Solaris server using SAS (speed is the benefit.)

    Although it is really difficult to estimate, is there a big boost in efficiency/memory
    usage/speed if I were to switch the process from Access to SQL Server?




    Thanks again,

    David Ramsey











    "david" <david@servihoo.com> wrote:
    >
    >Hi David,
    >
    >I don't know anything about SAS but if this might be of any help to you,
    >I would like to say that SQL Server, like any other Relational Database

    Management
    >System (RDBMS), suits most of the business information scenarios we might
    >think of and would therefore be much appropriate for your requirements too.
    >If you ever switch to SQL Server, you'll most certainly benefit from a wide
    >variety of features that would make your life easier. You would have Stored
    >Procedures, Views and Triggers amongst others to help you efficiently organise
    >and process your data and bcp features (for fast bulk copying data to and
    >from text files) as well as basic backup tools for your archiving needs.
    >I do certainly believe SQL Server is a valuable option for your business
    >requirements for the features, reliability and performance you might obviously
    >need. You can find more info in the Microsoft site at http://www.microsoft.com.
    >I develop solutions using VB 5.0 / SQL Server 6.5 / MS Access 97 and if

    you
    >think there are areas in which i might help you, please do feel free to

    mail
    >me.
    >
    >Regards,
    >
    >David
    >david@servihoo.com
    >
    >
    >"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
    >>
    >>Question from a SAS programmer:
    >>
    >>I don't have much experience with SQL Server, but have used the native

    SQL
    >>code to create datasets from DB2 tables.
    >>
    >>Here where I work, we use SAS primarily to do data processing - for example,
    >>a database of customer records that consists of an updated record for each
    >>customer at the end of each month - with the same variables (fields) such
    >>as the balance.
    >>
    >>We routinely take recordsets like this and transform them into one record
    >>for each with a renamed field for each previous duplicate field - balance_03_2001
    >>for example.
    >>
    >>We're trying to find alternatives to SAS. Is this something that SQL Server
    >>can do efficiently? Are there better alternatives? What would the solution
    >>to this problem look like?
    >>
    >>Thanks,
    >>
    >>David Ramsey

    >



  4. #4
    David Macknet Guest

    Re: Can Sql Server do this?


    David,

    Yes, SQL Server certainly can. SQL Server is not even something that you'd
    typically compare with Access (although many ask the question). If you're
    simply interested in sorting records and extracting results (with no updates
    to the dataset), SQL Server will allow you to do build indexes on the data
    "WITH FILLFACTOR 100" - meaning that all leaf nodes of the binary tree (of
    the index) are filled. This means that the index would be hideous if you
    were updating data, but will perform quite well for looking things up. This's
    just a small sample, but the power of the engine is tremendous. Even if
    you weren't to get fancy with fillfactors and pinning tables in memory, you'll
    be amazed at the sheer speed with which it operates. There's simply no comparison.

    To give you an example from an application I developed:
    Every night a job runs to pull in over 5 gigabytes of historical data from
    four different sources and summarize it in several ways. This summary operation
    takes a little over an hour. The users, with the end data, are able to extract
    statistical reports (further aggregating/summarizing the data). The users'
    reports take less than 45 seconds to run, even if they're aggregating over
    3 million rows.

    It was my first SQL Server Application, so I'm sure that I could've done
    better.

    Comparing Access to SQL Server is like comparing a Yugo to a Ferarri. Once
    you've seen the two....

    Good luck!

    -David

    "DavidR" <david.c.ramsey@bankofamerica.com> wrote:
    >
    >david,
    >
    >Thanks so much for the response - here's a scenario type question for you:
    >
    >I currently have a database that has upwards of 7 million records. MS Access,
    >as you can well imagine, is seriously slow in doing anything with this large
    >amount of data. I use Access to link to some RDBMS tables on another system,
    >and after pulling in the data that I need, the Access file can exceed 800
    >MB easily. Of course simply performing a sort on that amount of data can
    >take 30 min. to an hour (typically).
    >
    >This is why most of the processing to rearrange the data is done remotely
    >on a Unix based Solaris server using SAS (speed is the benefit.)
    >
    >Although it is really difficult to estimate, is there a big boost in efficiency/memory
    >usage/speed if I were to switch the process from Access to SQL Server?
    >
    >
    >
    >
    >Thanks again,
    >
    >David Ramsey
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >"david" <david@servihoo.com> wrote:
    >>
    >>Hi David,
    >>
    >>I don't know anything about SAS but if this might be of any help to you,
    >>I would like to say that SQL Server, like any other Relational Database

    >Management
    >>System (RDBMS), suits most of the business information scenarios we might
    >>think of and would therefore be much appropriate for your requirements

    too.
    >>If you ever switch to SQL Server, you'll most certainly benefit from a

    wide
    >>variety of features that would make your life easier. You would have Stored
    >>Procedures, Views and Triggers amongst others to help you efficiently organise
    >>and process your data and bcp features (for fast bulk copying data to and
    >>from text files) as well as basic backup tools for your archiving needs.
    >>I do certainly believe SQL Server is a valuable option for your business
    >>requirements for the features, reliability and performance you might obviously
    >>need. You can find more info in the Microsoft site at http://www.microsoft.com.
    >>I develop solutions using VB 5.0 / SQL Server 6.5 / MS Access 97 and if

    >you
    >>think there are areas in which i might help you, please do feel free to

    >mail
    >>me.
    >>
    >>Regards,
    >>
    >>David
    >>david@servihoo.com
    >>
    >>
    >>"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
    >>>
    >>>Question from a SAS programmer:
    >>>
    >>>I don't have much experience with SQL Server, but have used the native

    >SQL
    >>>code to create datasets from DB2 tables.
    >>>
    >>>Here where I work, we use SAS primarily to do data processing - for example,
    >>>a database of customer records that consists of an updated record for

    each
    >>>customer at the end of each month - with the same variables (fields) such
    >>>as the balance.
    >>>
    >>>We routinely take recordsets like this and transform them into one record
    >>>for each with a renamed field for each previous duplicate field - balance_03_2001
    >>>for example.
    >>>
    >>>We're trying to find alternatives to SAS. Is this something that SQL

    Server
    >>>can do efficiently? Are there better alternatives? What would the solution
    >>>to this problem look like?
    >>>
    >>>Thanks,
    >>>
    >>>David Ramsey

    >>

    >



  5. #5
    DavidR Guest

    Re: Can Sql Server do this?


    Thanks again! Since I have the Ferrari installation cd right here, it looks
    as though I will be driving much faster in days to come.





    "David Macknet" <david_t_macknet@agilent.com> wrote:
    >
    >David,
    >
    >Yes, SQL Server certainly can. SQL Server is not even something that you'd
    >typically compare with Access (although many ask the question). If you're
    >simply interested in sorting records and extracting results (with no updates
    >to the dataset), SQL Server will allow you to do build indexes on the data
    >"WITH FILLFACTOR 100" - meaning that all leaf nodes of the binary tree (of
    >the index) are filled. This means that the index would be hideous if you
    >were updating data, but will perform quite well for looking things up.

    This's
    >just a small sample, but the power of the engine is tremendous. Even if
    >you weren't to get fancy with fillfactors and pinning tables in memory,

    you'll
    >be amazed at the sheer speed with which it operates. There's simply no

    comparison.
    >
    >To give you an example from an application I developed:
    >Every night a job runs to pull in over 5 gigabytes of historical data from
    >four different sources and summarize it in several ways. This summary operation
    >takes a little over an hour. The users, with the end data, are able to

    extract
    >statistical reports (further aggregating/summarizing the data). The users'
    >reports take less than 45 seconds to run, even if they're aggregating over
    >3 million rows.
    >
    >It was my first SQL Server Application, so I'm sure that I could've done
    >better.
    >
    >Comparing Access to SQL Server is like comparing a Yugo to a Ferarri. Once
    >you've seen the two....
    >
    >Good luck!
    >
    >-David
    >
    >"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
    >>
    >>david,
    >>
    >>Thanks so much for the response - here's a scenario type question for you:
    >>
    >>I currently have a database that has upwards of 7 million records. MS

    Access,
    >>as you can well imagine, is seriously slow in doing anything with this

    large
    >>amount of data. I use Access to link to some RDBMS tables on another system,
    >>and after pulling in the data that I need, the Access file can exceed 800
    >>MB easily. Of course simply performing a sort on that amount of data

    can
    >>take 30 min. to an hour (typically).
    >>
    >>This is why most of the processing to rearrange the data is done remotely
    >>on a Unix based Solaris server using SAS (speed is the benefit.)
    >>
    >>Although it is really difficult to estimate, is there a big boost in efficiency/memory
    >>usage/speed if I were to switch the process from Access to SQL Server?
    >>
    >>
    >>
    >>
    >>Thanks again,
    >>
    >>David Ramsey
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>"david" <david@servihoo.com> wrote:
    >>>
    >>>Hi David,
    >>>
    >>>I don't know anything about SAS but if this might be of any help to you,
    >>>I would like to say that SQL Server, like any other Relational Database

    >>Management
    >>>System (RDBMS), suits most of the business information scenarios we might
    >>>think of and would therefore be much appropriate for your requirements

    >too.
    >>>If you ever switch to SQL Server, you'll most certainly benefit from a

    >wide
    >>>variety of features that would make your life easier. You would have Stored
    >>>Procedures, Views and Triggers amongst others to help you efficiently

    organise
    >>>and process your data and bcp features (for fast bulk copying data to

    and
    >>>from text files) as well as basic backup tools for your archiving needs.
    >>>I do certainly believe SQL Server is a valuable option for your business
    >>>requirements for the features, reliability and performance you might obviously
    >>>need. You can find more info in the Microsoft site at http://www.microsoft.com.
    >>>I develop solutions using VB 5.0 / SQL Server 6.5 / MS Access 97 and if

    >>you
    >>>think there are areas in which i might help you, please do feel free to

    >>mail
    >>>me.
    >>>
    >>>Regards,
    >>>
    >>>David
    >>>david@servihoo.com
    >>>
    >>>
    >>>"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
    >>>>
    >>>>Question from a SAS programmer:
    >>>>
    >>>>I don't have much experience with SQL Server, but have used the native

    >>SQL
    >>>>code to create datasets from DB2 tables.
    >>>>
    >>>>Here where I work, we use SAS primarily to do data processing - for example,
    >>>>a database of customer records that consists of an updated record for

    >each
    >>>>customer at the end of each month - with the same variables (fields)

    such
    >>>>as the balance.
    >>>>
    >>>>We routinely take recordsets like this and transform them into one record
    >>>>for each with a renamed field for each previous duplicate field - balance_03_2001
    >>>>for example.
    >>>>
    >>>>We're trying to find alternatives to SAS. Is this something that SQL

    >Server
    >>>>can do efficiently? Are there better alternatives? What would the solution
    >>>>to this problem look like?
    >>>>
    >>>>Thanks,
    >>>>
    >>>>David Ramsey
    >>>

    >>

    >



  6. #6
    david Guest

    Re: Can Sql Server do this?


    David Macknet has so nicely summarised the power of SQL Server. So, welcome
    to the SQL Server club David Ramsey. Seems Davids are all for SQL Server
    8-)

    "DavidR" <david.c.ramsey@bankofamerica.com> wrote:
    >
    >Thanks again! Since I have the Ferrari installation cd right here, it looks
    >as though I will be driving much faster in days to come.
    >
    >
    >
    >
    >
    >"David Macknet" <david_t_macknet@agilent.com> wrote:
    >>
    >>David,
    >>
    >>Yes, SQL Server certainly can. SQL Server is not even something that you'd
    >>typically compare with Access (although many ask the question). If you're
    >>simply interested in sorting records and extracting results (with no updates
    >>to the dataset), SQL Server will allow you to do build indexes on the data
    >>"WITH FILLFACTOR 100" - meaning that all leaf nodes of the binary tree

    (of
    >>the index) are filled. This means that the index would be hideous if you
    >>were updating data, but will perform quite well for looking things up.


    >This's
    >>just a small sample, but the power of the engine is tremendous. Even if
    >>you weren't to get fancy with fillfactors and pinning tables in memory,

    >you'll
    >>be amazed at the sheer speed with which it operates. There's simply no

    >comparison.
    >>
    >>To give you an example from an application I developed:
    >>Every night a job runs to pull in over 5 gigabytes of historical data from
    >>four different sources and summarize it in several ways. This summary

    operation
    >>takes a little over an hour. The users, with the end data, are able to

    >extract
    >>statistical reports (further aggregating/summarizing the data). The users'
    >>reports take less than 45 seconds to run, even if they're aggregating over
    >>3 million rows.
    >>
    >>It was my first SQL Server Application, so I'm sure that I could've done
    >>better.
    >>
    >>Comparing Access to SQL Server is like comparing a Yugo to a Ferarri.

    Once
    >>you've seen the two....
    >>
    >>Good luck!
    >>
    >>-David
    >>
    >>"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
    >>>
    >>>david,
    >>>
    >>>Thanks so much for the response - here's a scenario type question for

    you:
    >>>
    >>>I currently have a database that has upwards of 7 million records. MS

    >Access,
    >>>as you can well imagine, is seriously slow in doing anything with this

    >large
    >>>amount of data. I use Access to link to some RDBMS tables on another

    system,
    >>>and after pulling in the data that I need, the Access file can exceed

    800
    >>>MB easily. Of course simply performing a sort on that amount of data

    >can
    >>>take 30 min. to an hour (typically).
    >>>
    >>>This is why most of the processing to rearrange the data is done remotely
    >>>on a Unix based Solaris server using SAS (speed is the benefit.)
    >>>
    >>>Although it is really difficult to estimate, is there a big boost in efficiency/memory
    >>>usage/speed if I were to switch the process from Access to SQL Server?
    >>>
    >>>
    >>>
    >>>
    >>>Thanks again,
    >>>
    >>>David Ramsey
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>"david" <david@servihoo.com> wrote:
    >>>>
    >>>>Hi David,
    >>>>
    >>>>I don't know anything about SAS but if this might be of any help to you,
    >>>>I would like to say that SQL Server, like any other Relational Database
    >>>Management
    >>>>System (RDBMS), suits most of the business information scenarios we might
    >>>>think of and would therefore be much appropriate for your requirements

    >>too.
    >>>>If you ever switch to SQL Server, you'll most certainly benefit from

    a
    >>wide
    >>>>variety of features that would make your life easier. You would have

    Stored
    >>>>Procedures, Views and Triggers amongst others to help you efficiently

    >organise
    >>>>and process your data and bcp features (for fast bulk copying data to

    >and
    >>>>from text files) as well as basic backup tools for your archiving needs.
    >>>>I do certainly believe SQL Server is a valuable option for your business
    >>>>requirements for the features, reliability and performance you might

    obviously
    >>>>need. You can find more info in the Microsoft site at http://www.microsoft.com.
    >>>>I develop solutions using VB 5.0 / SQL Server 6.5 / MS Access 97 and

    if
    >>>you
    >>>>think there are areas in which i might help you, please do feel free

    to
    >>>mail
    >>>>me.
    >>>>
    >>>>Regards,
    >>>>
    >>>>David
    >>>>david@servihoo.com
    >>>>
    >>>>
    >>>>"DavidR" <david.c.ramsey@bankofamerica.com> wrote:
    >>>>>
    >>>>>Question from a SAS programmer:
    >>>>>
    >>>>>I don't have much experience with SQL Server, but have used the native
    >>>SQL
    >>>>>code to create datasets from DB2 tables.
    >>>>>
    >>>>>Here where I work, we use SAS primarily to do data processing - for

    example,
    >>>>>a database of customer records that consists of an updated record for

    >>each
    >>>>>customer at the end of each month - with the same variables (fields)

    >such
    >>>>>as the balance.
    >>>>>
    >>>>>We routinely take recordsets like this and transform them into one record
    >>>>>for each with a renamed field for each previous duplicate field - balance_03_2001
    >>>>>for example.
    >>>>>
    >>>>>We're trying to find alternatives to SAS. Is this something that SQL

    >>Server
    >>>>>can do efficiently? Are there better alternatives? What would the

    solution
    >>>>>to this problem look like?
    >>>>>
    >>>>>Thanks,
    >>>>>
    >>>>>David Ramsey
    >>>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center