Database

#realJSOP4-Jan-10 14:32

4-Jan-10 14:32

I dunno - the boss says it times out, shrugs his shoulders, and we all laugh a little at the problem.

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

Mycroft Holmes4-Jan-10 13:13

4-Jan-10 13:13

John Simmons / outlaw programmer wrote:
How bad would a stored proc have to be

Really crappy, I've seen this where SSMS performs the query in seconds and the UI takes ages. Is this the case?

Have you looked at the execution plan, it usually suggests indexes if there are glaring requirements. Otherwise just look for the most expensive operation and try and optimise it.

Never underestimate the power of human stupidity
RAH

Mycroft Holmes4-Jan-10 19:01

4-Jan-10 19:01

Ok so is this the same proc you are bitching about in the Lounge - and you need to ask the question.

Never underestimate the power of human stupidity
RAH

#realJSOP4-Jan-10 23:25

4-Jan-10 23:25

No (at least I don't think so). I'm not bitching about it either. Here, I asked a question. In the Liunge, I merely stated my amazement at somethig regarding the job.

Mycroft Holmes5-Jan-10 0:12

5-Jan-10 0:12

So how did the execution plan investigation go?

I just noted the coincidence of the enquiry here and the Lounge entry and wondered if the very slow proc was 1500 lines of unformatted tsql which would probably account for the slow response Big Grin | :-D

Never underestimate the power of human stupidity
RAH

David Skelly5-Jan-10 2:10

David Skelly

5-Jan-10 2:10

I'm not entirely sure what sort of response you're looking for here. How bad would a stored proc have to be? Well, I guess that depends on what it does. If it's a simple select, then it would have to be pretty bad. If it's a massive stored proc that does a zillion different things and only gets run once a year then I guess that it might not be badly written, it might just be slow and time-consuming, and you'll either have to live with it or find a different approach (don't do it all in one stored proc, break it down into manageable chunks, something like that).

I don't know that anyone will really be able to give you much advice beyond what you've already got from the others, certainly not without knowing more detail about the stored proc.

#realJSOP6-Jan-10 0:35

6-Jan-10 0:35

I don't really know any of the details. The only things I know are that "somewhere" during the process of doing whatever they do in the database, it times out and they can only get about 3000 records before it does. I was just looking for a starting point as to where to look. What makes it harder to be more specific is that they don't know what part of the process is timing out. Like I said, they just say it times out, they shrug, and everyone (except me) just kinda smiles about it.

The front-end is written in VB, so that should give you an idea of what I'm dealing with.

David Skelly6-Jan-10 2:24

David Skelly

6-Jan-10 2:24

It can be very hard to track these things down. We once had a stored proc that ran a query, returned some data, the front end then set a flag and saved the data back again. In development it completed within seconds. In live production use, it took 15 minutes.

When we looked into it, it turned out that the stored proc was actually running very quickly. So was the front-end processing to set the flag. All the time was being taken transferring the data across the network, because in the live database each row included a massive XML document that took forever to marshall off the database server, move it over the network and then marshall it all back for the front-end to receive. When the developer first tested it, the XML documents he used were teeny-tiny little things so it all ran quickly and no-one spotted the problem.

We rewrote the application so that the update was done in the stored proc without returning anything to the front end, and it finished in less than 10 seconds. When we released it to the users, we had people phoning us up saying, "I don't think this did anything, it was too quick..."

Tim Carmichael5-Jan-10 6:31

Tim Carmichael

5-Jan-10 6:31

Is it a stored procedure issue or a data issue?
We have a vendor supplied system where 90+ percent of the data is in one table.
We also have a user requested report that requires us to perform an inner join on said table with itself.

Tim

#realJSOP6-Jan-10 0:36

Updating Data on Other Server?? [modified]

6-Jan-10 0:36

I don't know, because none of the people that should know have any idea what's causing it or at what point in the process it's happening.

AmbiguousName4-Jan-10 7:05

4-Jan-10 7:05

Hi guys...I do not know whether it is right platform to ask the question. Suppose I update databse on my laptop. Now I want the same database on another location, to be updated automatically...obviously wirelessly. How to do that?? Confused | :confused:

modified on Monday, January 4, 2010 1:35 PM

Stryder_14-Jan-10 9:47

Stryder_1

4-Jan-10 9:47

Hi,

It will likely depend on the database you are using. Also, is this connected at the same time, or do you mean to have the database on your laptop updated, then when you connect to the other location, it updates at that time?

Two main ways:
1) Replication - Review this in your db documentation and return if you have further questions.
2) Do it yourself in code - code your app to send the changes when you connect the the remote database.

Thanks.

AmbiguousName5-Jan-10 3:47

5-Jan-10 3:47

There is Server1, Server2..... Server2 is nearer than Server1 at some location, to my device. Server2 is selected and the changes are written to the database on it. Now after this, changes should be automatically written to the Server1......hope made it clear to you. Having said this I"ll use your first suggestion.....Replication

Mycroft Holmes4-Jan-10 13:17

4-Jan-10 13:17

What happens to the updates if your wifi connection is down?

What happens when there are 2 or more local databases?

This is a really bad design. If you are working offline (local database) then you need to implement a replication strategy. This involves more than just shoving data back and forth.

Never underestimate the power of human stupidity
RAH

AmbiguousName5-Jan-10 3:54

5-Jan-10 3:54

Ans#1: what happens if your nearest ATM is not working well. You will have to wait untill it is fixed.
Ans#2: In case of 2 or more local DBs, nearest one will be selected to be updated.

Apart from this...im a student so any stupidity is hopefully forgiven Smile | :)

Mycroft Holmes5-Jan-10 11:05

5-Jan-10 11:05

Muzammil Saeed wrote:
Apart from this...im a student so any stupidity is hopefully forgiven

Absolutely

This is not necessarily stupid, just part of the learning process, what sort of system are you trying to design you have completely confused me with ATM, wireless and nearest database.

Never underestimate the power of human stupidity
RAH

AmbiguousName5-Jan-10 13:47

exported the first column of an excel file to a table

5-Jan-10 13:47

There is Server1, Server2..... Server2 is nearer than Server1 at some location, to my device.Hence Server2 is selected and the changes are written to the database on it. Now after this, changes should be automatically written to the Server1......hope made it clear to you

deva23-Jan-10 23:05

deva2

3-Jan-10 23:05

Hello
I work with sqlserver2005 express ,I try to populate my table student by:
the first field data by the first column of an excel file.
other field by 123.12 ...
My code is

INSERT INTO student (Ch1, ch2, ch3)<br />
SELECT ch1, 123 AS ch2, 124 AS ch3<br />
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',<br />
'Excel 8.0;Database=C:\test\xltest.xls', [Feuil1$])

but i have this mesage :
Unable to initialize the object from the data source OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Thanks Smile | :)

loop problem

Jassim Rahma3-Jan-10 21:02

3-Jan-10 21:02

I am using the following ocde to loop through existign records and create account summart record for every client but it seems looping with no end and it's creating an endless account summary records for the first client only.. what's wrong?

USE takhlees

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

DELETE FROM accounts_summary

-- reset primary key

INSERT INTO accounts_summary (account_number, account_name, core_account, is_read_only) VALUES (10000000001, 'CASH', 'True', 'True')
INSERT INTO accounts_summary (account_number, account_name, core_account, is_read_only) VALUES (10000000002, 'ACCOUNTS RECEIVABLE', 'True', 'True')

DECLARE @client_id int, @client_cpr varchar(50), @client_name varchar(255), @account_number_string VARCHAR(50), @account_number numeric(38,0)

DECLARE		account_numbers CURSOR FOR
SELECT		clients.client_id, clients.client_cpr, clients.client_name
FROM		clients
ORDER BY	clients.client_name

OPEN account_numbers

FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO accounts_summary (client_category, client_id, cr_cpr_number, account_number, account_name) VALUES ('I', @client_id, @client_cpr, SCOPE_IDENTITY(), @client_name)
	-- get random from 1 to 7
	SET @account_number_string = RIGHT('0' + convert(varchar(1), DATEPART(WEEKDAY, GETDATE())), 1)
	-- get random from 1 to 365
	SET @account_number_string = @account_number_string + RIGHT('000' + convert(varchar(3), DATEPART(DAYOFYEAR, GETDATE())), 3)
	SET @account_number_string = @account_number_string + RIGHT('0000000' + convert(varchar(7), SCOPE_IDENTITY()), 7)
	SET @account_number = convert(bigint, @account_number_string)
	UPDATE accounts_summary SET account_number = @account_number WHERE account_id = SCOPE_IDENTITY()
	UPDATE clients SET account_number = @account_number WHERE client_id = @client_id
	
	FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
END

CLOSE account_numbers
DEALLOCATE account_numbers

Jassim Rahma3-Jan-10 22:30

3-Jan-10 22:30

does it have anything to do with this:

WHILE @@FETCH_STATUS = 0

Jassim Rahma3-Jan-10 22:39

3-Jan-10 22:39

this is the modeified code ro make the SCOPE_IDENTITY clear but of course still the same problem

DECLARE @rec_id int, @client_id int, @client_cpr varchar(50), @client_name varchar(255), @account_number_string VARCHAR(50), @account_number numeric(38,0)

DECLARE		account_numbers CURSOR FOR
SELECT		clients.client_id, clients.client_cpr, clients.client_name
FROM		clients
ORDER BY	clients.client_name

OPEN account_numbers

FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO accounts_summary (client_category, client_id, cr_cpr_number, account_number, account_name) VALUES ('I', @client_id, @client_cpr, 0, @client_name)
	SET @rec_id = SCOPE_IDENTITY()
	-- get random from 1 to 7
	SET @account_number_string = RIGHT('0' + convert(varchar(1), DATEPART(WEEKDAY, GETDATE())), 1)
	-- get random from 1 to 365
	SET @account_number_string = @account_number_string + RIGHT('000' + convert(varchar(3), DATEPART(DAYOFYEAR, GETDATE())), 3)
	SET @account_number_string = @account_number_string + RIGHT('0000000' + convert(varchar(7), @rec_id), 7)
	SET @account_number = convert(bigint, @account_number_string)
	UPDATE accounts_summary SET account_number = @account_number WHERE account_id = @rec_id
	UPDATE clients SET account_number = @account_number WHERE client_id = @client_id
	
	FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
END

CLOSE account_numbers
DEALLOCATE account_numbers

Mycroft Holmes3-Jan-10 23:45

3-Jan-10 23:45

Dammed if I can see any problem with your cursor. Try breaking it down to just print the @client_id for each loop to make sure it is performing correctly.

I have an issue with you using the @account_number as a FK between client and account. You should be using the @rec_id as the FK on the client table. Keys should be stupid, you know no intelligence and you are using a formatted string as the key instead of the identity value - bad design!

Never underestimate the power of human stupidity
RAH

Jassim Rahma4-Jan-10 2:01

4-Jan-10 2:01

i did PRINT and it's just printing one record# 25

Corporal Agarn4-Jan-10 3:56

Corporal Agarn

4-Jan-10 3:56

Try just this

DECLARE		account_numbers CURSOR FOR
SELECT		clients.client_id, clients.client_cpr, clients.client_name
FROM		clients
ORDER BY	clients.client_name

OPEN account_numbers

FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Client_ID

	FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
END

CLOSE account_numbers
DEALLOCATE account_numbers

Also make sure your select statement is returning what you think.