Database

J4amieC9-Sep-09 2:15

J4amieC

9-Sep-09 2:15

This is a bit of a can of worms... If I was sitting down to design the architecture for a distrubuted application like this from scratch, i'd be very tempted to expose the data as services (web/wcf/whatever) and have applications at each site use these services.

However, it seems you're doing a straight database connection from each site to the central database - which is fine, but as you move further away from the database you have network latency and connectivity issues. Which is I think what you're asking about.

In terms of a solution - your suggestion of replicated database for Dubai is one way of solving the problem. You could also accept that the connection from any site to the database may slow down/fail and make your applications resilient to this (they should already be!). Perhaps think about hosting the database at a highly reliable site, with a fast connection - such as one of the big hosted data centres.

Simon P Stevens9-Sep-09 3:26

Simon P Stevens

9-Sep-09 3:26

Thanks J4amieC,

With regards to data services yes I kind of agree here, given a choice from scratch I think I'd probably go down the same route, unfortunately this is an existing system that has grown beyond it's original intentions (as is so common with these kind of things) and I don't think a re-architecture like this is really an option - although it does have a nicely separated DAL so the option is there. We are actually building another system in a distributed style like this for exactly these kind of reasons.

Yes I am kind of asking about latency & connectivity etc. The application currently has an online and offline mode and falls back silently into offline mode if any database connection errors occur, and attempts to reconnect every 15 minutes, so the system is already fairly resilient to that kind of outage.

I'm also asking if there is anything else I should be aware of with the above approach.

Finally I'm interested if anyone has any experience with a replicated database. It's not something I've ever tried, and I'd have concerns that there would be problems, but as I understand it there are features like this that are transparently built into SQL server so I could just configure it and then carry on as if it was just a normal local database. I'd be concerned about how it would handle conflicts and locks.

Simon

Johan Hakkesteegt10-Sep-09 0:10

Johan Hakkesteegt

10-Sep-09 0:10

Hi,

Just thinking out loud, mind you, so for what it is worth:

The main question is, what sort of data would you need at each site, at the same time, that must be in absolute real time sync?

The different sites of your company have been established for a reason, and I suppose it is not because all the people just won't fit into a single building. So I am guessing each site has its own customers, or vendors, or specialized work crew, or warehouse, or something.
That means that although each site will be organized with the same application(s), each will still mostly be working with unique data.
In other words, each site will be creating its own orders, deliveries, invoices, or whatever other business objects, that are specifically related to that site.

So adding site identifiers to all business objects in your database, should allow you to have each site run its own server/database/copy of the application. You can keep a central database at a single location that is updated every night (or whenever), through synchronization, or even simpler, using xml files. This central database could send certain common master data to each site's own database in turn, every night, hour, 15 minutes, etc. depending on requirements.
Usually even things like warehouse stock levels needn't be more up to date then 5 minutes.

This approach shouldn't need any kind of drastic measures, and might even offer greater flexibility for future development.

Cheers,

Johan

My advice is free, and you may get what you paid for.

Searching marathi(non-eglish) databse

Simon P Stevens11-Sep-09 4:21

Simon P Stevens

11-Sep-09 4:21

Thanks Johan,

There's some good points in there. Like you say, the company is divided by sites for a reason, and yes large parts of the data is specifically for an individual site. I'm going to take a look at each table and decide how up to date it needs to be with respect to each site.

Thanks for the suggestions.

Simon

girishdonde8-Sep-09 21:45

girishdonde

8-Sep-09 21:45

Hi, I am working on application that have following basic requirement
• Data(name) will be stored in Marathi (non-English) language
• During search user will type english charachter from keybord and the result will be shown in Marathi.
Does anyone have idea how to achieve this? Is it possible to store marathi DB in access?
Thanks in advance.

Re: Searching marathi(non-eglish) databse

Mycroft Holmes8-Sep-09 22:42

Re: Searching marathi(non-eglish) databse

8-Sep-09 22:42

Have some google mojo[^] that should get you the answer on storing unicode data in a database.

As for typing english and searching marathi data, you would need to translate one language to the other. You cannot search for an orange in the apple orchard.

Luc Pattyn9-Sep-09 1:52

Luc Pattyn

9-Sep-09 1:52

mighty mojo you have.

Smile | :)

Luc Pattyn

Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.

SQL 2008 Query Help

Hulicat8-Sep-09 16:01

Hulicat

8-Sep-09 16:01

Why would this simple select error like this: sql 2008?

SELECT field1

FROM ADDRESS
where field1 != 'NULL'

"Error Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric."

The real goal is to get this select to work but there are two fields causing me problems. The two fields commented out are generating the same error as above....how would I insert a cast into the below query for fields 4 and 5?

use dbname
Select field1 + ' ' + ISNULL(field2,'')
+ ' ' + ISNULL(field3,'')
--+ ' ' + ISNULL(field4,'')
--+ ' ' + ISNULL(field5,'')
as column_new from table1

Error converting data type varchar to numeric."

Regards,
Hulicat

_Damian S_8-Sep-09 17:34

8-Sep-09 17:34

Hulicat wrote:
SELECT field1

FROM ADDRESS
where field1 != 'NULL'

select field1 from ADDRESS where field1 is not null

Hulicat wrote:
how would I insert a cast into the below query for fields 4 and 5?

select cast(field5 as varchar)

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

Hulicat8-Sep-09 18:20

Hulicat

8-Sep-09 18:20

Thanks for the reply.

I am having an issue inserting the cast into this statement for feilds 4, 5:

use dbname
Select field1 + ' ' + ISNULL(field2,'')
+ ' ' + ISNULL(field3,'')
--+ ' ' + ISNULL(field4,'')
--+ ' ' + ISNULL(field5,'')
as column_new from table1

Regards,
Hulicat

_Damian S_8-Sep-09 18:55

8-Sep-09 18:55

Hulicat wrote:
I am having an issue inserting the cast into this statement for feilds 4, 5:

I already gave you that answer...

Here it is in total since you seem to have not understood what I was talking about...

use dbname
Select field1 + ' ' + ISNULL(field2,'')
+ ' ' + ISNULL(field3,'')
+ ' ' + cast(field4 as varchar)
+ ' ' + cast(field5 as varchar)
as column_new from table1

Mycroft Holmes8-Sep-09 21:11

8-Sep-09 21:11

When you use the cast as per damians suggestion you need to define the size of the varchar as int

ISNULL(Cast Field4 as varchar(20)),'')

Hulicat9-Sep-09 5:31

Hulicat

9-Sep-09 5:31

Thanks that was jamming me up....got it thanks.

Regards,
Hulicat

_Damian S_9-Sep-09 14:25

9-Sep-09 14:25

Mycroft Holmes wrote:
you need to define the size of the varchar as int

Interesting... not in SQL-2005 you don't!!

Mycroft Holmes9-Sep-09 14:35

9-Sep-09 14:35

I am using 2008 and checked BOL before posting, I use Convert almost exclusively so I had to check, maybe the doco is out of date?

_Damian S_9-Sep-09 15:29

9-Sep-09 15:29

Maybe... I did it with SQL2005 before posting... don't have SQL2008 here...

which answer is right?

mctramp1688-Sep-09 14:47

mctramp168

8-Sep-09 14:47

I have a table containing production information that your users query frequently, They specifically use this
query most often (that is only use name to search in the where condition):
SELECT Name,Description,Vendor,Instock,Price FROM Products where Name='name'

Have a nonclustered index on this table on the Name column,but your users are complaining the query is still too
slow,what can you do to speed it up?
A、modify the index to include the Description,vendor,Instock, and price columns as nonkey columns.
B、Create a new nonclustered index on the Description,vendor,Instock, and price as nonkey columns.
C、Create a new clustered index on the Description,vendor,Instock, and price as nonkey columns.
D、You can't do anything to speed up this query.

Database is MS SQL SERVER.
Above four choices, which answer is right?please tell the reason.Thanks

Ashfield8-Sep-09 21:05

Ashfield

8-Sep-09 21:05

Surely its obvious? Look at the select

SELECT Name,Description,Vendor,Instock,Price FROM Products where Name='name'

and the options:

B and C - why will creating these indices help? The search is on name only.
D - of course it can be speeded up

This leaves A. By including the other columns on the non-clustered index only the index will be read, therefore saving the read from the underlying table.

Hope this explains it, it really is a bit of a no-brainer question.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Mycroft Holmes8-Sep-09 21:20

8-Sep-09 21:20

You know it would never occur to me to add these other fields into the index, I would leave it at D. The penalty of AED on that table/index would outweigh the benefits I would have thought.

Taking that advice to the next level and you get something like - every table that is in a select query should carry an index with every field in it!

Ashfield9-Sep-09 1:31

Ashfield

9-Sep-09 1:31

Mycroft Holmes wrote:
The penalty of AED on that table/index would outweigh the benefits I would have thought.

Based on the OP "I have a table containing production information that your users query frequently" implies to me that there are many more reads than writes, so once the index is created there is little overhead - however, based on the info given, that is not certain so you may be correct. In real life you make a judgement call based on your knowledge of the system.

Mycroft Holmes wrote:
I would leave it at D

Not if you have a load of irate users on your back Smile | :)

In reality, the guy wanted to know the answer to an interview/exam question, and I would put my money on adding the other columns to the index being what they were looking for.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Mycroft Holmes9-Sep-09 14:48

9-Sep-09 14:48

Ashfield wrote:
In reality, the guy wanted to know the answer to an interview/exam question

It definitely feels like a theory question that has no basis in reality.

I would have trouble answering D as I know I don't know everything and I hate having to answer that to irate users, so I would be looking for the E option - more research required.