Showing posts with label relational. Show all posts
Showing posts with label relational. Show all posts

Wednesday, March 28, 2012

Optimizing cursor performance?

Hi there!
I have an application that uses cursors en mass in SQL. Actually, it does not
speak one word relational SQL with MSSQL, only cursors. I think it has a fair
bet in becoming World Champion in cursor abuse.
Anyway, the application is almost pure sequential/flat file based, and thus I
wondered, is there anything I can do to MSSQL that pulls out every bit of cursor
performance available, as it is very much needed?
Microsoft knows the problems, as they themself bought this very application..
I doubt, therefore I might be.
Kim
Cursors are never (mostly) good choice. Without seeing your data ,it's hard
to suggest something.
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>
|||I have found in many cases that declaring the cursor as Static can often
lead to increased performance but as always it depends<g>.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ov$xEZNvFHA.3864@.TK2MSFTNGP12.phx.gbl

> Cursors are never (mostly) good choice. Without seeing your data
> ,it's hard to suggest something.
I know, that's why I'm trying to be slightly sarcastic in my message .
What do you need of information? Unfortunately I can only pull out limited
information on this, as the application is closed source.
I doubt, therefore I might be.
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> I have found in many cases that declaring the cursor as Static can
> often lead to increased performance but as always it depends<g>.
But is there anything more 'high level' I can do? What does cursors appreciate
of hardware; same as with relational SQL?
The fun thing is that the application doesn't seem to care much about extra RAM.
Even when the SQL server sucks up 1.5GB versus 500MB, I still don't see any
performance improvements noteworthy.
Almost seems like there's some hardwired cap on max cursor performance .
I doubt, therefore I might be.
|||Depending on how they are configured and such they may use a lot of Tempdb.
So making sure tempdb is on a fast drive array might help. They tend to use
a fair amount of cpu as well. But there is no magic switch to make cursors
go faster. The real answer is sadly to rewrite them to not use cursors.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:udfAjDSvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> But is there anything more 'high level' I can do? What does cursors
> appreciate of hardware; same as with relational SQL?
> The fun thing is that the application doesn't seem to care much about
> extra RAM. Even when the SQL server sucks up 1.5GB versus 500MB, I still
> don't see any performance improvements noteworthy.
> Almost seems like there's some hardwired cap on max cursor performance .
> --
> I doubt, therefore I might be.
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e50F7YSvFHA.3580@.TK2MSFTNGP10.phx.gbl

> Depending on how they are configured and such they may use a lot of
> Tempdb. So making sure tempdb is on a fast drive array might help.
> They tend to use a fair amount of cpu as well. But there is no magic
> switch to make cursors go faster. The real answer is sadly to
> rewrite them to not use cursors.
I tried "performance monitor" the tempdb while pounding it with nasty cursors
through the application. However I don't seen any kind of activity in the
tempdb. Looking at the database the application uses, I get a "log byte
flushed/sec" activity (about 61kb) every 4-5 second or so.
Monitoring CPU usage I get around ~60% on a hyperthread Intel P4. "Monitoring
Avg. Disk Transfer" I get numbers far below the capacity of the disk (it's a WDC
Raptor 10k rpm).
So I'm not really any closer figuring out where the bottleneck is regarding
cursor abuse.
I doubt, therefore I might be.

Optimizing cursor performance?

Hi there!
I have an application that uses cursors en massé in SQL. Actually, it does not
speak one word relational SQL with MSSQL, only cursors. I think it has a fair
bet in becoming World Champion in cursor abuse.
Anyway, the application is almost pure sequential/flat file based, and thus I
wondered, is there anything I can do to MSSQL that pulls out every bit of cursor
performance available, as it is very much needed?
Microsoft knows the problems, as they themself bought this very application..
--
I doubt, therefore I might be.Kim
Cursors are never (mostly) good choice. Without seeing your data ,it's hard
to suggest something.
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en massé in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||I have found in many cases that declaring the cursor as Static can often
lead to increased performance but as always it depends<g>.
--
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en massé in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ov$xEZNvFHA.3864@.TK2MSFTNGP12.phx.gbl
> Cursors are never (mostly) good choice. Without seeing your data
> ,it's hard to suggest something.
I know, that's why I'm trying to be slightly sarcastic in my message :).
What do you need of information? Unfortunately I can only pull out limited
information on this, as the application is closed source.
--
I doubt, therefore I might be.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> I have found in many cases that declaring the cursor as Static can
> often lead to increased performance but as always it depends<g>.
But is there anything more 'high level' I can do? What does cursors appreciate
of hardware; same as with relational SQL?
The fun thing is that the application doesn't seem to care much about extra RAM.
Even when the SQL server sucks up 1.5GB versus 500MB, I still don't see any
performance improvements noteworthy.
Almost seems like there's some hardwired cap on max cursor performance :).
--
I doubt, therefore I might be.|||Depending on how they are configured and such they may use a lot of Tempdb.
So making sure tempdb is on a fast drive array might help. They tend to use
a fair amount of cpu as well. But there is no magic switch to make cursors
go faster. The real answer is sadly to rewrite them to not use cursors.
--
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:udfAjDSvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
>> I have found in many cases that declaring the cursor as Static can
>> often lead to increased performance but as always it depends<g>.
> But is there anything more 'high level' I can do? What does cursors
> appreciate of hardware; same as with relational SQL?
> The fun thing is that the application doesn't seem to care much about
> extra RAM. Even when the SQL server sucks up 1.5GB versus 500MB, I still
> don't see any performance improvements noteworthy.
> Almost seems like there's some hardwired cap on max cursor performance :).
> --
> I doubt, therefore I might be.
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e50F7YSvFHA.3580@.TK2MSFTNGP10.phx.gbl
> Depending on how they are configured and such they may use a lot of
> Tempdb. So making sure tempdb is on a fast drive array might help.
> They tend to use a fair amount of cpu as well. But there is no magic
> switch to make cursors go faster. The real answer is sadly to
> rewrite them to not use cursors.
I tried "performance monitor" the tempdb while pounding it with nasty cursors
through the application. However I don't seen any kind of activity in the
tempdb. Looking at the database the application uses, I get a "log byte
flushed/sec" activity (about 61kb) every 4-5 second or so.
Monitoring CPU usage I get around ~60% on a hyperthread Intel P4. "Monitoring
Avg. Disk Transfer" I get numbers far below the capacity of the disk (it's a WDC
Raptor 10k rpm).
So I'm not really any closer figuring out where the bottleneck is regarding
cursor abuse.
--
I doubt, therefore I might be.

Optimizing cursor performance?

Hi there!
I have an application that uses cursors en mass in SQL. Actually, it does n
ot
speak one word relational SQL with MSSQL, only cursors. I think it has a fai
r
bet in becoming World Champion in cursor abuse.
Anyway, the application is almost pure sequential/flat file based, and thus
I
wondered, is there anything I can do to MSSQL that pulls out every bit of cu
rsor
performance available, as it is very much needed?
Microsoft knows the problems, as they themself bought this very application.
.
I doubt, therefore I might be.Kim
Cursors are never (mostly) good choice. Without seeing your data ,it's hard
to suggest something.
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||I have found in many cases that declaring the cursor as Static can often
lead to increased performance but as always it depends<g>.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ov$xEZNvFHA.3864@.TK2MSFTNGP12.phx.gbl

> Cursors are never (mostly) good choice. Without seeing your data
> ,it's hard to suggest something.
I know, that's why I'm trying to be slightly sarcastic in my message .
What do you need of information? Unfortunately I can only pull out limited
information on this, as the application is closed source.
I doubt, therefore I might be.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> I have found in many cases that declaring the cursor as Static can
> often lead to increased performance but as always it depends<g>.
But is there anything more 'high level' I can do? What does cursors apprecia
te
of hardware; same as with relational SQL?
The fun thing is that the application doesn't seem to care much about extra
RAM.
Even when the SQL server sucks up 1.5GB versus 500MB, I still don't see any
performance improvements noteworthy.
Almost seems like there's some hardwired cap on max cursor performance .
I doubt, therefore I might be.|||Depending on how they are configured and such they may use a lot of Tempdb.
So making sure tempdb is on a fast drive array might help. They tend to use
a fair amount of cpu as well. But there is no magic switch to make cursors
go faster. The real answer is sadly to rewrite them to not use cursors.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:udfAjDSvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> But is there anything more 'high level' I can do? What does cursors
> appreciate of hardware; same as with relational SQL?
> The fun thing is that the application doesn't seem to care much about
> extra RAM. Even when the SQL server sucks up 1.5GB versus 500MB, I still
> don't see any performance improvements noteworthy.
> Almost seems like there's some hardwired cap on max cursor performance .
> --
> I doubt, therefore I might be.
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e50F7YSvFHA.3580@.TK2MSFTNGP10.phx.gbl

> Depending on how they are configured and such they may use a lot of
> Tempdb. So making sure tempdb is on a fast drive array might help.
> They tend to use a fair amount of cpu as well. But there is no magic
> switch to make cursors go faster. The real answer is sadly to
> rewrite them to not use cursors.
I tried "performance monitor" the tempdb while pounding it with nasty cursor
s
through the application. However I don't seen any kind of activity in the
tempdb. Looking at the database the application uses, I get a "log byte
flushed/sec" activity (about 61kb) every 4-5 second or so.
Monitoring CPU usage I get around ~60% on a hyperthread Intel P4. "Monitorin
g
Avg. Disk Transfer" I get numbers far below the capacity of the disk (it's a
WDC
Raptor 10k rpm).
So I'm not really any closer figuring out where the bottleneck is regarding
cursor abuse.
I doubt, therefore I might be.

Monday, March 26, 2012

Optimized way of Importing more than 300000 lines of xml data into SQL

Hi,

We have a 120 MB huge relational xml file, that we parse it through xsd file and import it into SQL Server relational tables using .net Dataset class. It takes around 2 hours to validate and import this huge file into sql server.

When the import is happening most of the Server CPU and memory resources are also more than 50%.

We would like to increase the performance of this import and we are ready to redesign the application if we find a better solution.

Any ideas/suggestions sincerely appreciated.

javed.

Hi Javed,

You can use XML Bulk Load component to import your xml data directly into SQL Server.

Refer Books on line SQL Server 2005 :

SQL Server 2005 Books online ->

SQL Server Programming Reference ->

SQL XML 4.0 Programming ->

Performing Bulk Load of XML Data (SQLXML 4.0)

Refer the following links to know more about XmlBulkLoad component:

Performing Bulk Load of XML Data (SQLXML 4.0)

Using SQL Server's XML Support > XML Bulk Load

How to import XML into SQL Server with the XML Bulk Load component

Thanks

Naras.

Friday, March 9, 2012

Opinions needed

I am somewhat new to database technologies, I only have a basic
understanding of relational databases.
I need to figure out the best way to set up a database (Tables and fields).
Crystal reports will be used for reporting.
I am in the distribution business and I need to create route lists for
people who will go out and deliver catalogs to our clients customers. Our
client will send me their database of customers via tab or comma delimited
file. What I then need to do is import this file into SQL and then output
some nice looking reports for our carriers delivery routes.
The routes will be based on streets in a logical geographical area which I
hope I will be able to organize by postal code. (Since postal codes are
already organized for optimal delivery time and coverage)
I should add that I'm not even sure I need to use SQL with Crystal, why
wouldn't I use Access and just use the reporting features in Access?
Any help will be appreciated.
Shawn JohnsonIf it's a multi-user application you should use SQL Server.
If it's just a single user import and print application, Access probably
will do.
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> I am somewhat new to database technologies, I only have a basic
> understanding of relational databases.
> I need to figure out the best way to set up a database (Tables and
fields).
> Crystal reports will be used for reporting.
> I am in the distribution business and I need to create route lists for
> people who will go out and deliver catalogs to our clients customers. Our
> client will send me their database of customers via tab or comma delimited
> file. What I then need to do is import this file into SQL and then output
> some nice looking reports for our carriers delivery routes.
> The routes will be based on streets in a logical geographical area which I
> hope I will be able to organize by postal code. (Since postal codes are
> already organized for optimal delivery time and coverage)
> I should add that I'm not even sure I need to use SQL with Crystal, why
> wouldn't I use Access and just use the reporting features in Access?
> Any help will be appreciated.
> Shawn Johnson
>|||How many users? How much data? How's your budget? How's your experience
with SQL Server and/or Access?
Either can do what you've told us, but you've not told us enough. :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> I am somewhat new to database technologies, I only have a basic
> understanding of relational databases.
> I need to figure out the best way to set up a database (Tables and
fields).
> Crystal reports will be used for reporting.
> I am in the distribution business and I need to create route lists for
> people who will go out and deliver catalogs to our clients customers. Our
> client will send me their database of customers via tab or comma delimited
> file. What I then need to do is import this file into SQL and then output
> some nice looking reports for our carriers delivery routes.
> The routes will be based on streets in a logical geographical area which I
> hope I will be able to organize by postal code. (Since postal codes are
> already organized for optimal delivery time and coverage)
> I should add that I'm not even sure I need to use SQL with Crystal, why
> wouldn't I use Access and just use the reporting features in Access?
> Any help will be appreciated.
> Shawn Johnson
>|||I will interject a little bit of my prferences and expertise on
this...Crystal is a much more robust of a reprting tool than Access. If you
are just developing reports so that users can print them out etc...you can
import everything into Access, and use access as the reporting tool and
create a few nifty little buttons and forms to make it all user friendly
etc...However, one great point that was mentioned was if you are going to
have many people connect to this database and do thier own importing and
reporting and manipulation and you plan on developing an application to
interface with this...Then SQL is the way to go, using crystal reports to
interface with the data. Well crystal won't change the data, but it will
dress up the reports real perty like!
Good luck!
Luis
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> How many users? How much data? How's your budget? How's your experience
> with SQL Server and/or Access?
> Either can do what you've told us, but you've not told us enough. :-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> fields).
Our
delimited
output
I
>|||I will be the only user to output the reports to send to my carriers
As far as how much data is concerned, it depends on the region we are
delivering to, sometimes 20000 all the way up to 250000
Budget is whatever it takes (Software or training) to get the job done
properly.
My experiance with SQL and Access is just as an admin. (Adding and removing
fields for a CRM application and other small admin tasks, nothing to
complex) but I guess I would consider myself a fairly quick study. I do
have some time on my side since we are getting our existing route lists from
another "routing" company. We are merely positioning ourselves to takeover
in several months.
Let me know if you have any other Qs
Shawn J
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> How many users? How much data? How's your budget? How's your experience
> with SQL Server and/or Access?
> Either can do what you've told us, but you've not told us enough. :-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> fields).
Our
delimited
output
I
>|||Do you think Access can handle up to 350000 different customer records?
"Nico De Greef" <ndg@.denco.be> wrote in message
news:OsRinnI$DHA.3232@.TK2MSFTNGP10.phx.gbl...
> If it's a multi-user application you should use SQL Server.
> If it's just a single user import and print application, Access probably
> will do.
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> fields).
Our
delimited
output
I
>|||I don't think Access is optimized for handling that amount of records.
You are the only user using the tool, it could be a little overkill to buy a
server and a SQL Server license just for your import and print task.
You could also use SQL Desktop Edition, it is also limited on the multi user
side but is closer to SQL Server than Access. You won't need a separate
server for this one.
If you should ever need to upgrade to SQL Server it is easier to start from
SQL Server desktop then from Access.
I never use Access, but only because I have the hardware available to run
SQL Server.
Based on your info I would start with SQL Desktop Edition and a reporting
tool of your choice.
Can Access handle the reporting part of the job ? One answer: test it.
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:OKpluIK$DHA.712@.tk2msftngp13.phx.gbl...
> Do you think Access can handle up to 350000 different customer records?
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:OsRinnI$DHA.3232@.TK2MSFTNGP10.phx.gbl...
> Our
> delimited
> output
which
> I
are
why
>|||This is just my guess that you will be doing more
development in future, most Access applications end up
being migrated to sql server anyway. So I would recommend
to use sql server from the start.

>--Original Message--
>I will be the only user to output the reports to send to
my carriers
>As far as how much data is concerned, it depends on the
region we are
>delivering to, sometimes 20000 all the way up to 250000
>Budget is whatever it takes (Software or training) to get
the job done
>properly.
>My experiance with SQL and Access is just as an admin.
(Adding and removing
>fields for a CRM application and other small admin tasks,
nothing to
>complex) but I guess I would consider myself a fairly
quick study. I do
>have some time on my side since we are getting our
existing route lists from
>another "routing" company. We are merely positioning
ourselves to takeover
>in several months.
>Let me know if you have any other Qs
>Shawn J
>
>
>"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote
in message
>news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
How's your experience
us enough. :-)
have a basic
database (Tables and
create route lists for
clients customers.
>Our
tab or comma
>delimited
into SQL and then
>output
routes.
geographical area which
>I
(Since postal codes are
coverage)
with Crystal, why
features in Access?
>
>.
>

Opinions needed

I am somewhat new to database technologies, I only have a basic
understanding of relational databases.
I need to figure out the best way to set up a database (Tables and fields).
Crystal reports will be used for reporting.
I am in the distribution business and I need to create route lists for
people who will go out and deliver catalogs to our clients customers. Our
client will send me their database of customers via tab or comma delimited
file. What I then need to do is import this file into SQL and then output
some nice looking reports for our carriers delivery routes.
The routes will be based on streets in a logical geographical area which I
hope I will be able to organize by postal code. (Since postal codes are
already organized for optimal delivery time and coverage)
I should add that I'm not even sure I need to use SQL with Crystal, why
wouldn't I use Access and just use the reporting features in Access?
Any help will be appreciated.
Shawn JohnsonIf it's a multi-user application you should use SQL Server.
If it's just a single user import and print application, Access probably
will do.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> I am somewhat new to database technologies, I only have a basic
> understanding of relational databases.
> I need to figure out the best way to set up a database (Tables and
fields).
> Crystal reports will be used for reporting.
> I am in the distribution business and I need to create route lists for
> people who will go out and deliver catalogs to our clients customers. Our
> client will send me their database of customers via tab or comma delimited
> file. What I then need to do is import this file into SQL and then output
> some nice looking reports for our carriers delivery routes.
> The routes will be based on streets in a logical geographical area which I
> hope I will be able to organize by postal code. (Since postal codes are
> already organized for optimal delivery time and coverage)
> I should add that I'm not even sure I need to use SQL with Crystal, why
> wouldn't I use Access and just use the reporting features in Access?
> Any help will be appreciated.
> Shawn Johnson
>|||How many users? How much data? How's your budget? How's your experience
with SQL Server and/or Access?
Either can do what you've told us, but you've not told us enough. :-)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> I am somewhat new to database technologies, I only have a basic
> understanding of relational databases.
> I need to figure out the best way to set up a database (Tables and
fields).
> Crystal reports will be used for reporting.
> I am in the distribution business and I need to create route lists for
> people who will go out and deliver catalogs to our clients customers. Our
> client will send me their database of customers via tab or comma delimited
> file. What I then need to do is import this file into SQL and then output
> some nice looking reports for our carriers delivery routes.
> The routes will be based on streets in a logical geographical area which I
> hope I will be able to organize by postal code. (Since postal codes are
> already organized for optimal delivery time and coverage)
> I should add that I'm not even sure I need to use SQL with Crystal, why
> wouldn't I use Access and just use the reporting features in Access?
> Any help will be appreciated.
> Shawn Johnson
>|||I will interject a little bit of my prferences and expertise on
this...Crystal is a much more robust of a reprting tool than Access. If you
are just developing reports so that users can print them out etc...you can
import everything into Access, and use access as the reporting tool and
create a few nifty little buttons and forms to make it all user friendly
etc...However, one great point that was mentioned was if you are going to
have many people connect to this database and do thier own importing and
reporting and manipulation and you plan on developing an application to
interface with this...Then SQL is the way to go, using crystal reports to
interface with the data. Well crystal won't change the data, but it will
dress up the reports real perty like! :)
Good luck!
Luis
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> How many users? How much data? How's your budget? How's your experience
> with SQL Server and/or Access?
> Either can do what you've told us, but you've not told us enough. :-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> > I am somewhat new to database technologies, I only have a basic
> > understanding of relational databases.
> >
> > I need to figure out the best way to set up a database (Tables and
> fields).
> > Crystal reports will be used for reporting.
> >
> > I am in the distribution business and I need to create route lists for
> > people who will go out and deliver catalogs to our clients customers.
Our
> > client will send me their database of customers via tab or comma
delimited
> > file. What I then need to do is import this file into SQL and then
output
> > some nice looking reports for our carriers delivery routes.
> >
> > The routes will be based on streets in a logical geographical area which
I
> > hope I will be able to organize by postal code. (Since postal codes are
> > already organized for optimal delivery time and coverage)
> >
> > I should add that I'm not even sure I need to use SQL with Crystal, why
> > wouldn't I use Access and just use the reporting features in Access?
> >
> > Any help will be appreciated.
> >
> > Shawn Johnson
> >
> >
>|||I will be the only user to output the reports to send to my carriers
As far as how much data is concerned, it depends on the region we are
delivering to, sometimes 20000 all the way up to 250000
Budget is whatever it takes (Software or training) to get the job done
properly.
My experiance with SQL and Access is just as an admin. (Adding and removing
fields for a CRM application and other small admin tasks, nothing to
complex) but I guess I would consider myself a fairly quick study. I do
have some time on my side since we are getting our existing route lists from
another "routing" company. We are merely positioning ourselves to takeover
in several months.
Let me know if you have any other Qs
Shawn J
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> How many users? How much data? How's your budget? How's your experience
> with SQL Server and/or Access?
> Either can do what you've told us, but you've not told us enough. :-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> > I am somewhat new to database technologies, I only have a basic
> > understanding of relational databases.
> >
> > I need to figure out the best way to set up a database (Tables and
> fields).
> > Crystal reports will be used for reporting.
> >
> > I am in the distribution business and I need to create route lists for
> > people who will go out and deliver catalogs to our clients customers.
Our
> > client will send me their database of customers via tab or comma
delimited
> > file. What I then need to do is import this file into SQL and then
output
> > some nice looking reports for our carriers delivery routes.
> >
> > The routes will be based on streets in a logical geographical area which
I
> > hope I will be able to organize by postal code. (Since postal codes are
> > already organized for optimal delivery time and coverage)
> >
> > I should add that I'm not even sure I need to use SQL with Crystal, why
> > wouldn't I use Access and just use the reporting features in Access?
> >
> > Any help will be appreciated.
> >
> > Shawn Johnson
> >
> >
>|||Do you think Access can handle up to 350000 different customer records?
"Nico De Greef" <ndg@.denco.be> wrote in message
news:OsRinnI$DHA.3232@.TK2MSFTNGP10.phx.gbl...
> If it's a multi-user application you should use SQL Server.
> If it's just a single user import and print application, Access probably
> will do.
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> > I am somewhat new to database technologies, I only have a basic
> > understanding of relational databases.
> >
> > I need to figure out the best way to set up a database (Tables and
> fields).
> > Crystal reports will be used for reporting.
> >
> > I am in the distribution business and I need to create route lists for
> > people who will go out and deliver catalogs to our clients customers.
Our
> > client will send me their database of customers via tab or comma
delimited
> > file. What I then need to do is import this file into SQL and then
output
> > some nice looking reports for our carriers delivery routes.
> >
> > The routes will be based on streets in a logical geographical area which
I
> > hope I will be able to organize by postal code. (Since postal codes are
> > already organized for optimal delivery time and coverage)
> >
> > I should add that I'm not even sure I need to use SQL with Crystal, why
> > wouldn't I use Access and just use the reporting features in Access?
> >
> > Any help will be appreciated.
> >
> > Shawn Johnson
> >
> >
>|||I don't think Access is optimized for handling that amount of records.
You are the only user using the tool, it could be a little overkill to buy a
server and a SQL Server license just for your import and print task.
You could also use SQL Desktop Edition, it is also limited on the multi user
side but is closer to SQL Server than Access. You won't need a separate
server for this one.
If you should ever need to upgrade to SQL Server it is easier to start from
SQL Server desktop then from Access.
I never use Access, but only because I have the hardware available to run
SQL Server.
Based on your info I would start with SQL Desktop Edition and a reporting
tool of your choice.
Can Access handle the reporting part of the job ? One answer: test it.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
news:OKpluIK$DHA.712@.tk2msftngp13.phx.gbl...
> Do you think Access can handle up to 350000 different customer records?
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:OsRinnI$DHA.3232@.TK2MSFTNGP10.phx.gbl...
> > If it's a multi-user application you should use SQL Server.
> > If it's just a single user import and print application, Access probably
> > will do.
> >
> > --
> > Nico De Greef
> > Belgium
> > Freelance Software Architect
> > MCP, MCSD, .NET certified
> >
> > "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
> > news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
> > > I am somewhat new to database technologies, I only have a basic
> > > understanding of relational databases.
> > >
> > > I need to figure out the best way to set up a database (Tables and
> > fields).
> > > Crystal reports will be used for reporting.
> > >
> > > I am in the distribution business and I need to create route lists for
> > > people who will go out and deliver catalogs to our clients customers.
> Our
> > > client will send me their database of customers via tab or comma
> delimited
> > > file. What I then need to do is import this file into SQL and then
> output
> > > some nice looking reports for our carriers delivery routes.
> > >
> > > The routes will be based on streets in a logical geographical area
which
> I
> > > hope I will be able to organize by postal code. (Since postal codes
are
> > > already organized for optimal delivery time and coverage)
> > >
> > > I should add that I'm not even sure I need to use SQL with Crystal,
why
> > > wouldn't I use Access and just use the reporting features in Access?
> > >
> > > Any help will be appreciated.
> > >
> > > Shawn Johnson
> > >
> > >
> >
> >
>|||This is just my guess that you will be doing more
development in future, most Access applications end up
being migrated to sql server anyway. So I would recommend
to use sql server from the start.
>--Original Message--
>I will be the only user to output the reports to send to
my carriers
>As far as how much data is concerned, it depends on the
region we are
>delivering to, sometimes 20000 all the way up to 250000
>Budget is whatever it takes (Software or training) to get
the job done
>properly.
>My experiance with SQL and Access is just as an admin.
(Adding and removing
>fields for a CRM application and other small admin tasks,
nothing to
>complex) but I guess I would consider myself a fairly
quick study. I do
>have some time on my side since we are getting our
existing route lists from
>another "routing" company. We are merely positioning
ourselves to takeover
>in several months.
>Let me know if you have any other Qs
>Shawn J
>
>
>"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote
in message
>news:unxbQIJ$DHA.2664@.TK2MSFTNGP09.phx.gbl...
>> How many users? How much data? How's your budget?
How's your experience
>> with SQL Server and/or Access?
>> Either can do what you've told us, but you've not told
us enough. :-)
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>> "Shawn Johnson" <sejohnson77@.yahoo.com> wrote in message
>> news:%23mxZQkI$DHA.2040@.TK2MSFTNGP12.phx.gbl...
>> > I am somewhat new to database technologies, I only
have a basic
>> > understanding of relational databases.
>> >
>> > I need to figure out the best way to set up a
database (Tables and
>> fields).
>> > Crystal reports will be used for reporting.
>> >
>> > I am in the distribution business and I need to
create route lists for
>> > people who will go out and deliver catalogs to our
clients customers.
>Our
>> > client will send me their database of customers via
tab or comma
>delimited
>> > file. What I then need to do is import this file
into SQL and then
>output
>> > some nice looking reports for our carriers delivery
routes.
>> >
>> > The routes will be based on streets in a logical
geographical area which
>I
>> > hope I will be able to organize by postal code.
(Since postal codes are
>> > already organized for optimal delivery time and
coverage)
>> >
>> > I should add that I'm not even sure I need to use SQL
with Crystal, why
>> > wouldn't I use Access and just use the reporting
features in Access?
>> >
>> > Any help will be appreciated.
>> >
>> > Shawn Johnson
>> >
>> >
>>
>
>.
>