Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

EFFICIENT Front End for SQL Server Expand / Collapse
Author
Message
Posted Monday, June 30, 2014 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 3:03 PM
Points: 6, Visits: 30
I know there's been many questions about SQL front ends, but I have some specific requirements I haven't seen addressed.

I'm an intern for a company with a large database and I've been tasked with creating a front end so people with no SQL experience can query the database. The company uses SQL Server 2008 R2, but a lot of the users need some sort of front end application. They shouldn't be able to update tables or anything like that, just read from the database and ask for specific information.

They currently have a program to do this, but the two problems are it's written in C# and they don't have anybody that really knows C# (I do, but am only here for the summer.), and the queries are not efficient enough, as they have a very large database and very many people accessing it, and so it's having a major impact on performance.

The front end that they want has to be able to be supported after I leave, so they don't want it in C#. I'm not sure if there's any program for creating interfaces that could be easily done with no programming experience, but that seems to rule out a lot of possibilities. The other possibilities seem to be some sort of open-source project, or access.

Access seems like a good bet because it's easy to use for people who would be accessing it, and also it seems easy to set up. The question is how optimized would these queries be? Would the SQL code resulting from this be fast? Especially because the users don't know anything about that. Does Access handle this itself? Is there a way for us to influence what Access outputs when users input things?

Are there any other open-source applications that could be used for these purposes? As I said, efficiency of the queries is very key, as well as being able to be supported without much experience with programming.
Post #1587657
Posted Monday, June 30, 2014 9:16 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:25 PM
Points: 592, Visits: 7,022
Hrm. My previous employer used Access for its operations and later scaled up to SQL Server, with Access remaining as the front end. It worked, but there's some problems with the implementation.

First, having people use Access to get to data in SQL can potentially be a minefield of trouble. If your front-end users don't understand programming well, as you indicated, their queries may end up like a tangled blob of spaghetti. They'll get their results (maybe!), but it's entirely possible that the queries they use will be horribly inefficient, or, painfully enough, non-deterministic (so the results will vary based on executions).

Second, Access functions best with a SQL Server backend when the queries you write for it are pass-through queries. This style of query lets you write the SQL code directly, which is executed on the server, and the results are passed back to Access for displaying.

Without a pass-through query, Access will instead request the entirety of the dataset from the SQL Server, operate on it, and then display the results. Because Access is not nearly as efficient as SQL Server, this will kill the performance, especially if the datasets you're working with are quite large. If the datasets are relatively small, however, it won't be as bad.

I suppose it's important to question the company's current setup; do they have any programmers, or a DBA? From your description, they don't, and that's all kinds of bad. If they don't have a DBA, they have nobody to tune the SQL queries with, nevermind maintenance and backups. If the statistics are horribly out of date on the database, and the database isn't indexed well, nothing the front end can do will ever help with optimization. The same goes for backups and corruption checks; if nobody's there to implement those, the entire business is dead in the water the moment something corrupts (and it might already be corrupted somewhere, if nobody's checked for it!).

It sounds like what the business needs is a solid programmer and a solid DBA, at minimum. I might be ahead of myself here, but a business that relies on in-house programmed systems cannot survive without the personnel to actually maintain and extend those systems. Granted, if the proper automation is set up to ensure the livelihood of the system, and all user needs have been accounted for, sure, that gets the problem out of the way. But it's unlikely this will be the case in a complex business.

Err... Well, soapboxing aside, that's my bit on the subject . In summary, Access is workable, though with major pitfalls that your users can easily drop into, and I'd highly suggest the business be motivated into getting the necessary personnel as soon as possible.




-
Post #1587691
Posted Monday, June 30, 2014 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 3:03 PM
Points: 6, Visits: 30
Thanks for the response, let me clarify a bit.

There is indeed a DBA (two), who handle the things in microsoft SQL server. They are often making cubes, running reports, and making queries for people. But while they do SQL they do not have as much experience with making applications, and are not that proficient in C# and such languages. They are working in SQL server directly.

The application is so other users in the company (say the financial department or marketing) who do not have SQL experience can look into some things themselves, at a lower level than the cubes would provide. It's to work as a front end to SQL Server.

The C# application they use now gives the users dropdowns and options that they can search for, and it then creates the query, but it isn't doing this efficiently, which is a major problem since some of the data tables are so large. Without the ability to support the application, they can't really fix it.

While I could probably look into that myself, they don't want to rely on something that they don't have the resources to support. That's why they were hoping to use Access or some other program that could do what they want to do. So as I said it would need to be easy to use for those who don't know SQL and would make sure the queries are efficient. Access seemed like a good bet since it's so easy to set up and use, but I'm just worried about the optimization of queries from there. And you seem to also think that could be a problem.

So is there an option either A. an open-source program that would serve the purpose and also be smart about the queries without the user actually having to know SQL, or B. some sort of way of creating this that is much easier to learn than C# and thus could be possibly supported.
Post #1587698
Posted Monday, June 30, 2014 9:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
So what they want is a custom application so that users with no technical ability can query the database. It must be fast because the current application is not fast enough. It must be written in a language so that users with no technical ability can modify the code. About the only thing I can think of would be to let the DBAs write views/stored procedures for the data required. And drop the results into a grid in Access. The biggest issue is that this type of solution can either fast or it can be easily maintainable by non technical people. I just don't see how you will get both.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1587700
Posted Monday, June 30, 2014 9:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 3:03 PM
Points: 6, Visits: 30
I would think the speed would be the bigger issue. Basically the queries have to be smartly made. Something that will take what people want to receive, and be smart about how they do the joins and selects and things like that. I figured that there would possibly be some application (maybe even Access) that would do that automatically, but wasn't sure.

It will be non-technical people using the software, but as I said there are DBAs and so it's not like there are NO technical people to maintain the software. It just couldn't be a dense C# program or something. If it was maybe something simpler that was not as hard to understand then I could probably make a case for it.

Post #1587704
Posted Monday, June 30, 2014 9:55 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:25 PM
Points: 592, Visits: 7,022
Ah, gotcha! That changes things up a good bit .

Access may well be a more workable solution, then, provided the DBAs aren't overwhelmed presently. Pass-through queries are literally done the same way as SQL queries, since you're writing the statement that SQL Server will use to return data.

If the DBAs have the time, users could submit query requests much like they submit report requests; if they need dropdowns and so on to retrieve data, pass-through queries could be written to pull that data, supply it to Access forms, and the forms would drive the data retrieval.

There would be a bit of a learning curve for the DBAs, but it's at least GUI-based; the pass-throughs would retrieve the data, and they'd be specified as a data source in the Access form designs. There's also the matter of designing the forms, which may or may not be an issue (it's like designing a fairly simplistic GUI, but depending on the complexity needed, there could be lots of moving parts).

Granted, if the DBAs are strained at present, that's going to muddy things up a bit, but if they're willing to work on learning a bit about the functionality of Access, it could be a workable solution.




-
Post #1587709
Posted Monday, June 30, 2014 10:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
Andrew Kernodle (6/30/2014)
Ah, gotcha! That changes things up a good bit .

Access may well be a more workable solution, then, provided the DBAs aren't overwhelmed presently. Pass-through queries are literally done the same way as SQL queries, since you're writing the statement that SQL Server will use to return data.

If the DBAs have the time, users could submit query requests much like they submit report requests; if they need dropdowns and so on to retrieve data, pass-through queries could be written to pull that data, supply it to Access forms, and the forms would drive the data retrieval.

There would be a bit of a learning curve for the DBAs, but it's at least GUI-based; the pass-throughs would retrieve the data, and they'd be specified as a data source in the Access form designs. There's also the matter of designing the forms, which may or may not be an issue (it's like designing a fairly simplistic GUI, but depending on the complexity needed, there could be lots of moving parts).

Granted, if the DBAs are strained at present, that's going to muddy things up a bit, but if they're willing to work on learning a bit about the functionality of Access, it could be a workable solution.


I would recommend NOT using pass through sql in any application. Otherwise your application needs to be changed when something in the query isn't quite right. I would use stored procedures and/or views so the queries can be modified on the sql side and the application doesn't need to change at all.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1587714
Posted Monday, June 30, 2014 10:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 3:03 PM
Points: 6, Visits: 30
Andrew Kernodle (6/30/2014)
Ah, gotcha! That changes things up a good bit .

Access may well be a more workable solution, then, provided the DBAs aren't overwhelmed presently. Pass-through queries are literally done the same way as SQL queries, since you're writing the statement that SQL Server will use to return data.

If the DBAs have the time, users could submit query requests much like they submit report requests; if they need dropdowns and so on to retrieve data, pass-through queries could be written to pull that data, supply it to Access forms, and the forms would drive the data retrieval.

There would be a bit of a learning curve for the DBAs, but it's at least GUI-based; the pass-throughs would retrieve the data, and they'd be specified as a data source in the Access form designs. There's also the matter of designing the forms, which may or may not be an issue (it's like designing a fairly simplistic GUI, but depending on the complexity needed, there could be lots of moving parts).

Granted, if the DBAs are strained at present, that's going to muddy things up a bit, but if they're willing to work on learning a bit about the functionality of Access, it could be a workable solution.


Can you go into a little more detail? I'm a little muddy on this.

Are you saying that the users would say what they wanted in the report and then the DBA would actually write the SQL for it? It's unfortunately not really an option since I think part of what has driven this is indeed to lighten the load of the DBA to work on other matters. If the other departments could order these queries themselves that would save time for the DBAs who spend a lot of time doing this for them. There are a fairly large number of people in the company.

Let me know if I misinterpreted you since I'm still a little rusty on this.

As for stored procedures and views, can I also get a bit more detail into that? Would that be basically selecting pre-made queries or would there be some flexibility with this?

I'm hoping Access is workable in some way as it seems like the easiest solution, but I want to again emphasize that some of the tables are VERY large. For example a table of transactions has millions of entries. Even just querying that table alone by itself takes two minutes to finish selecting all the rows. So when you're now joining that table with other tables or not efficiently filtering your results, you run into major performance problems. And I know that the columns you select, the indexes, what order you filter in, and how you do your joins all can make major major differences in performance.
Post #1587717
Posted Monday, June 30, 2014 10:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
mjd327 (6/30/2014)

I'm hoping Access is workable in some way as it seems like the easiest solution, but I want to again emphasize that some of the tables are VERY large. For example a table of transactions has millions of entries. Even just querying that table alone by itself takes two minutes to finish selecting all the rows. So when you're now joining that table with other tables or not efficiently filtering your results, you run into major performance problems. And I know that the columns you select, the indexes, what order you filter in, and how you do your joins all can make major major differences in performance.


This is exactly what I figured. If there was a tool out there that could generate efficient queries dynamically there wouldn't be much need for technical people who understand how to write efficient queries. If your end users need to be able to see all the rows in the table and then filter them there isn't much you can do. This type of application is difficult if not impossible to write correctly.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1587724
Posted Monday, June 30, 2014 10:44 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:25 PM
Points: 592, Visits: 7,022
Sean Lange (6/30/2014)
Andrew Kernodle (6/30/2014)
Ah, gotcha! That changes things up a good bit .

Access may well be a more workable solution, then, provided the DBAs aren't overwhelmed presently. Pass-through queries are literally done the same way as SQL queries, since you're writing the statement that SQL Server will use to return data.

If the DBAs have the time, users could submit query requests much like they submit report requests; if they need dropdowns and so on to retrieve data, pass-through queries could be written to pull that data, supply it to Access forms, and the forms would drive the data retrieval.

There would be a bit of a learning curve for the DBAs, but it's at least GUI-based; the pass-throughs would retrieve the data, and they'd be specified as a data source in the Access form designs. There's also the matter of designing the forms, which may or may not be an issue (it's like designing a fairly simplistic GUI, but depending on the complexity needed, there could be lots of moving parts).

Granted, if the DBAs are strained at present, that's going to muddy things up a bit, but if they're willing to work on learning a bit about the functionality of Access, it could be a workable solution.


I would recommend NOT using pass through sql in any application. Otherwise your application needs to be changed when something in the query isn't quite right. I would use stored procedures and/or views so the queries can be modified on the sql side and the application doesn't need to change at all.


Gah, I should've been more specific . Pass-through queries that call stored procedures was what I meant, but it wasn't what I said . And being specific is what definitely counts in this sort of a situation! Thanks for the correction.

EDIT: As for the previous post, yes, I was thinking that the DBAs would be doing most of the engineering of the system. However, since they're in a time crunch, that seems like a less usable option.

In that case, we're back to square one; Access would indeed let your end-users write their own queries, but the performance devastation that could occur would be painful.

Also, you lose the benefit of a centralized development location; if everyone's writing their own queries, and something in the data or database needs to be altered, some of those queries will malfunction. If nobody's keeping track of what everybody's writing, it becomes a painstaking process of going to each user, getting their queries, and adjusting them to work with the new format. This may or may not be a common occurrence at your workplace. However, if it does happen, there's going to have to be someone that fixes the problems; it would seem that would be a DBA, which may be problematic, given their limited time.

The Access approach may not be as feasible if the DBAs can't exercise control over the medium; that being the case, I'm not certain of a workable solution for this issue, myself. Hopefully someone else can chime in with more assistance!




-
Post #1587725
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse