June 30, 2014 at 8:31 am
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.
June 30, 2014 at 9:16 am
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.
- 😀
June 30, 2014 at 9:29 am
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.
June 30, 2014 at 9:33 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2014 at 9:42 am
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.
June 30, 2014 at 9:55 am
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.
- 😀
June 30, 2014 at 10:03 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2014 at 10:12 am
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.
June 30, 2014 at 10:36 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2014 at 10:44 am
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 :-D. 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!
- 😀
June 30, 2014 at 10:48 am
Ok, I just had a talk with one of my bosses and got some more information on what he had in mind.
I didn't realize what he meant at first, but what he was hoping was that rather than having it be created dynamically (as the application is doing now) we would be able to have them access (through Access) a smaller view/table that we create ourselves.
For example, the different departments will only need some specific tables and columns, which is a small subset of the total tables and columns. My boss was hoping we could do all of the joins ourselves, and create sort of views/tables that would be faster to query from. We could also edit the indexes and things like that, so that way that using Access the users can access THOSE tables and get their results much faster than needing to join a table with a million entries and tons of columns, most of which are unnecessary for their use.
June 30, 2014 at 11:03 am
mjd327 (6/30/2014)
Ok, I just had a talk with one of my bosses and got some more information on what he had in mind.I didn't realize what he meant at first, but what he was hoping was that rather than having it be created dynamically (as the application is doing now) we would be able to have them access (through Access) a smaller view/table that we create ourselves.
For example, the different departments will only need some specific tables and columns, which is a small subset of the total tables and columns. My boss was hoping we could do all of the joins ourselves, and create sort of views/tables that would be faster to query from. We could also edit the indexes and things like that, so that way that using Access the users can access THOSE tables and get their results much faster than needing to join a table with a million entries and tons of columns, most of which are unnecessary for their use.
That is more manageable. I would build those views on the sql side and let access retrieve that data.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2014 at 1:05 pm
I have to admit I have never tried this, but I am wondering if the SSRS report builder with a suitable set of models would be a compromise that worked, as it could be set up to allow certain users access to certain models, but those models would be under the control of the DBAs.
Just wondering...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 1, 2014 at 7:55 am
Sean Lange (6/30/2014)
mjd327 (6/30/2014)
Ok, I just had a talk with one of my bosses and got some more information on what he had in mind.I didn't realize what he meant at first, but what he was hoping was that rather than having it be created dynamically (as the application is doing now) we would be able to have them access (through Access) a smaller view/table that we create ourselves.
For example, the different departments will only need some specific tables and columns, which is a small subset of the total tables and columns. My boss was hoping we could do all of the joins ourselves, and create sort of views/tables that would be faster to query from. We could also edit the indexes and things like that, so that way that using Access the users can access THOSE tables and get their results much faster than needing to join a table with a million entries and tons of columns, most of which are unnecessary for their use.
That is more manageable. I would build those views on the sql side and let access retrieve that data.
Okay, I think I'll experiment with SQL Server and Access later today. I guess I'd want to make views of joined tables. That should cut down a lot of time, especially if they always are searching for the same columns, which it looks like they are. Since it looks like they need like 30 fields from this massive table and are joining it with like 1-2 fields from like 3 other tables, it should be pretty easy to just join those fields to the table (I haven't played with views before but I'm assuming I can.)
Can I do something like have multiple tables, one of the entries from the past 5 days, then 30 days, then 60 days, and then when they search for a date range I check the correct table? Because that would probably also help with speed.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply