Showing posts with label experience. Show all posts
Showing posts with label experience. Show all posts

Friday, March 30, 2012

need help and advise with database

need help and advise with database...

i am an independant oil and gas consultant and have a fair amount of computer experience but, am lost when it comes to data bases ... i found a database online that contains names, addresses, phone numbers, fax numbers and email addresses (live links) of companies and people within the oil and gas industry ... i want to be able to utilize this db in excel so i can query and use excels "mail merge" system to send them emails... the db online is in the .php format.

i want to show my appreciation by paying something for this service ... and i will.

i have queried the db online for all entries located within the united states and received over 70,000 entries ... i then copied and pasted them to microsoft word ... it is now 174 pages .. i have it saved to a file ... i can send the file to you.

again, my goal is to use this db in excel. ... please let me know if you might be interested in doing this and if you nedd any additional information.

thx,

jerrywhat u mean to say
u have the usersinformation in word and want to transfer this into
excel or sql
i have not understood make it little bit clear|||Jerry

When you received the records were they in comma separated format (i.e. were the Name, Address, contact, etc all separated with a comma. If they were then just copy the original text file and change the file extension to .csv instead of .txt. Then open the file from excel and save as an excel file.

Mary

Monday, March 19, 2012

Need an allround DBA (development&Admin) in MSSQL and Oracle

All,

International company in Australia needs an allround DBA
(development&Admin) in MSSQL and Oracle.

If you have 5+ experience in development and (not OR) Administration in
both MSSQL and Oracle and like challenges, please send CV to
rooty_hill2002@.yahoo.com.au.

I will send the position description to suitable candidates.

Cheers,

YakSorry, the email box is not shown properly. It should be
rooty_hill2002@.yahoo.com.au|||I don't know what happened! The email box is still not shown properly!
It should have "rooty_hill2002" in front of @.!

Monday, March 12, 2012

Need advice on reading material

Hello,

I'm new to SQL Server, have alot of experience with DB2 and Oracle,
and have been
tasked with supporting SQL Server. Could anyone suggest a good
administrator book?
Thanks!!

Dave SchaefferHi Dave

You don't say which version of SQL server you are going to use? Books online
is always a good place to start and the articles on MSDN and Technet are
always useful. You may want to look through some of the books on
http://www.microsoft.com/technet/pr...ning/books.mspx
although it didn't show the William Stanek books
http://www.microsoft.com/MSpress/books/6794.asp for which there are both SQL
2000 and SQL 2005 editions.

The best thing may be to get down to a good bookshop and browse!

John

"Dave" <dave.schaeffer@.gmail.comwrote in message
news:1157653347.633362.53010@.d34g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

Hello,
>
I'm new to SQL Server, have alot of experience with DB2 and Oracle,
and have been
tasked with supporting SQL Server. Could anyone suggest a good
administrator book?
Thanks!!
>
>
Dave Schaeffer
>

Need advice on most performant option...

Hi there,
I'm looking for advices from people with more experience using SQL Server. I have a situation where from my perspective i have 3 ways to accomplish my task, but i dont know which way would be more efficient and performant.
Here's the deal, i have a table which holds millions of records. This table will eventually receives between 30-40 insertion a second. So it's pretty busy. To display the data correctly in reports over the web, i need to manipulate the data in such a way that certain transaction are modified and others are completly eliminated depending on a set of preferences choosen at the moment of the request for the report. Obviously i dont see any ways to do this with just one SQL query, so my choices are the following:
1. Create a second table, which would hold the manipulated data. Create a trigger and on each insertion manipulate the data and modify the second table.
2. Create a stored procedure, from the stored procedure i query my records, create a temp table, manipulate my data and return it to the user
3. Simply fetch the data, return it to the IIS server and process it there before returning the result to the client.
The manipulation process consists of querying about 1000 to 2000 records, eliminating duplicate results (or similar results within a chosen range) by comparing them to the last one, and to indicate for each record how many other records were similar. In other words, it's a basic loop and i compare each record to the last one to see if it changed.
Personally i think choice #2 would be better, but i could be wrong. I'd like to hear your comments and suggestions.
Thanks,

Would the resultset start with 1000-2000 rows or would that be the total after you manipulated the data?
If you go with choice 2 would you use a cursor? If you must use a cursor then I would go with choice 3. (Given that the results going to your apsx page is 1000-2000 rows)

|||

the resultset would start at 1000-2000 rows, but then would shrink down to 100-500 rows after processing. If i did go with choice #2, i dont see any other way but to use a cursor to fetch my initial dataset (~1500 rows), loop through that and insert into temp table. But i just found that i can use a datatype of type table instead of a temp table. And it's supposed to be more efficient than a temp table. What other options would i have besides a cursor?
So your thoughts are that if my cursor fetches ~1500 rows, that's less efficient than doing the process directly on my IIS server? Can i ask why? i've heard that cursors are to be avoided at all cost but i'm not really sure why.
Thanks for the input! :)

|||

Why do you think this cannot be done with one SQL query? You'd be surprised what can be done in a single statement.

|||Having 30-40 concurrent users each fetching a large dataset would tax the system.
I do avoid cursors at all costs and have always managed to stay away from them, except this week; I had no way out and used one for a particular solution. :(
One other question; are you inserting any data back into the database as you loop throught the resultset?|||

no i'm not inserting data again into the database from within my loop

...

and the reason i think it's not possible to do it in a single query statement, is because i've tried. I'm not necessarly an expert in SQL, but i consider myself pretty good at it. More with Oracle than SQL Server. But i am willing to agree that it could be possible, if someone wants to show me! :)

After carefull though, i'll probably do it on the server side. The load on my IIS server will be less than on my Database server and i think .NET will process it faster than SQL Server would with a cursor.

Thanks

|||Since you're not doing any inserts, I agree that you should let .NET handle it.|||Is SQL Server running on the same machine as IIS? You say there are 30-40 inserts a second, but how often is that data likely to be queried?

Also, don't forget that a combination of all 3 options might be the most efficient. There may be some changes that can be done on insert by triggers, some when querying the data and some in .NET istelf. Personally I would start to think something was wrong with your database design if you were returning 1000-2000 records in query and then throwing most rows away.|||

currently, for our development phase, yes both IIS and SQL Server are on the same machine, but that's not gonna be the case for production

As far as database design, i think it's good. We're not throwing away any rows...simply elliminating them from the resultset, but we still need them to calculate various results for rows we do want to show in our resultset. You could call it process control, devices send me data every few seconds, and i need to process that data to visually display each devices behavior. And when the device's properties dont change, instead of showing 50 rows with the same data, i'll onlly show 1 row indicating the device and it's accumulated data and results.

I'll keep in mind to use all 3 options though, that could help.

Thanks

|||

XiNull wrote:

... instead of showing 50 rows with the same data, i'll onlly show 1 row indicating the device and it's accumulated data and results.

It sounds like your aggregating, could you not use a Group By in your Select?