Timeline for Why can't I just let customers connect directly to my database?
Current License: CC BY-SA 4.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 9, 2022 at 14:50 | comment | added | Kashif Faraz | Agreed with this one advantage of web service and it is not limited to this one. but my concern is only SQL DATABASE performance. Can you share any performance related comparison between two approaches I mentioned earlier. You may define any scenario which may impact on performance. I read some articles , one is : "When simultaneous client requests are made, application performance degrades rapidly due to the fact that clients necessitate separate connections and increased CPU memory. " medium.com/@paulndemo/2-and-3-tier-architecture-4a473e5ced3d | |
| Nov 8, 2022 at 21:16 | comment | added | Lawnmower Man | Assuming the employee records are not huge, this example is small enough to have minimal performance impact. Everything will reside in memory even on the SQL Server. The advantage of a web service is if multiple apps connect to the DB. When you do maintenance on the DB, the apps can still serve read-only requests, or it can transparently redirect to a clone of the DB. If apps connect directly to DB, every app needs to do this chore. | |
| Nov 8, 2022 at 7:23 | comment | added | Kashif Faraz | assumed I have employee table with 1000 rows and I query select * from employee. and I have two types of architecture for same data application 1. Desktop app clients directly connect to sql and 2. Desktop App Clients https request to webserver and webserver request to database, in both cases I run same query select * from employee and also same number of clients (100 or more) . Now my question is that in which one is better for sql performance? in others words, different 100 clients 100 connections vs same 1 client (Webserver) 100 connections? Note: no using any type cache (redis) | |
| Nov 5, 2022 at 19:15 | comment | added | Lawnmower Man | @KashifFaraz that is unanswerable without seeing the queries being run and the size of the DB. If the clients are running small queries which touch hundreds of rows then SQL Server can probably run everything in-memory and the impact is negligible. If DB has a billion records and users are doing full table scans, then 100 of them can easily crush the server. | |
| Nov 5, 2022 at 11:24 | comment | added | Kashif Faraz | client (100 user) directly connect to sql vs client (100 user) to web server to database, what will be the performance impact on SQL Server? | |
| Apr 20, 2020 at 18:13 | comment | added | MrZander | Caching is a big one for us. Our app was designed for LANs and connects directly to an on-premise SQL server, and even with < 100 users at a time, some of the bigger queries can bring the entire server to a halt when 10 users run them at the same time. Implementing a HTTP/SignalR layer has resulted in huge performance gains due to caching and not requiring the client to poll for changes. I can't even imagine trying to optimize for a web-scale service all with direct connections. | |
| Apr 18, 2020 at 8:59 | comment | added | Ruther Rendommeleigh | Minor nitpick: I wouldn't call everyone who abuses the DB malicious or incompetent. Part of the point of separating front and back end devs is that a frontender doesn't need to master SQL or remember which fields are indexed (most of the time). Hand them the keys to the DB and that goes out the window. | |
| Apr 18, 2020 at 1:37 | comment | added | user1937198 | And it does depend on the exact pricing structure and mechanisms. I'm more familiar with AWS, and I definitely know there are a bunch of ways to shot yourself in the foot with secondary bills like S3 there. | |
| Apr 18, 2020 at 1:30 | comment | added | user1937198 | If you set it up that way. Its less common to think about that in a company which doesn't use internal billing, but where the internal client is far enough away from the operators not to think about costs | |
| Apr 18, 2020 at 0:32 | comment | added | NPSF3000 | @user1937198 to be fair, at least as far as BigQuery is concerned, you can share data while letting customers use their own billing projects to pay for usage. BigQuery is a good way to share large scale SQL data - but to be clear that's very different from what OP is asking. | |
| Apr 17, 2020 at 23:54 | comment | added | user1937198 | Whilst not necessarily the case for a traditional server database, if you use a one of the SaaS big data databases like BigQuery or Aurora, that malicious performance issue can turn into an ability to run up large bills to your company extremely quickly. I once came across an unexpected $2000 bill, because the internal client was naive about the storage of the data in question. If they had been malicious, they could have run up $20-30,000 before we noticed. | |
| Apr 17, 2020 at 23:03 | review | First posts | |||
| Apr 18, 2020 at 1:46 | |||||
| Apr 17, 2020 at 22:57 | history | answered | Lawnmower Man | CC BY-SA 4.0 |