|
Lol yeah sorry for the type O
i edited the post
Cheers
|
|
|
|
|
|
Jtai wrote:
Select field1,field2,field3
From table1
Where type="EDU"
When I read the code, I thought it was a pity that Active wasn't in the DB so you could restrict the number of lines based on Active. Then I thought, don't even run the logic if Active isn't true. As I continued reading, I realized the real condition. Did you drop the Active check in the code along with modifying the select?
If it's still a large # of rows being read, you might want to include your condition checks in the selection criteria. Also, hopefully, you are really running a sproc to get your data.
Here's an example of using 4 condition checks with very different selection criteria. If two condition checks overlap then the data produced would overlap. The table had 5K rows in it, the selection had 39 rows in it.
declare @tbl table (Cond int, sTotID int, stot1 int, stot2 int, stot3 int)
insert into @tbl
values (1,-1, 20, -1, 49), (2, 203148, -1, -1, -1), (3, -1, 15, 29, -1), (4, -1, 25, 32, -1)
SELECT TOP 1000 Cond, TotID
,NumbRecs
,tot1
,tot2
,tot3
FROM dbo.ThreeNumberSum
JOIN @tbl on (sTotID = -1 or sTotID = TotID) AND (stot1 = -1 OR stot1 = tot1)
AND (stot2 = -1 OR stot2 = tot2) AND (stot3 = -1 OR stot3 = tot3)
Your code could just check 1 value to match all the multiple conditions. Here is an extract of all the rows:
Cond TotID NumbRecs tot1 tot2 tot3
1 202749 32 20 27 49
... middle number 28-30
1 203149 208 20 31 49
1 203249 184 20 32 49
1 203349 128 20 33 49
1 203449 56 20 34 49
1 203549 8 20 35 49
1 203649 8 20 36 49
2 203148 424 20 31 48
3 152940 24 15 29 40
3 152941 40 15 29 41
3 152942 32 15 29 42
3 152943 48 15 29 43
3 152944 8 15 29 44
3 152945 8 15 29 45
4 253228 28956 25 32 28
4 253229 49698 25 32 29
... last number 30-35
4 253236 595694 25 32 36
4 253237 529858 25 32 37
... last number 38-48
4 253249 8 25 32 49
|
|
|
|
|
tecgoblin wrote: It's surprising how many people try to create their own solutions to problems which already have established and well-behaving solution :S.
In the spirit of this comment I wanted to introduce a new standard - the DAO Framework. Problem: "Some day you may wish to change databases." Solution: "Create a generic database access framework to ease migration."
Example:
Firstly, define a DBConst file as follows:
public class DBConst {
public static String SELECT = "SELECT";
public static String WHERE = "WHERE";
public static String AND = "AND"
etc
}
Then create an interface: SQLFactory as follows:
public interface SQLFactory {
public String createSQL(String[] selectors, String[] whereClauses);
}
If I remember correctly, now all you have to do is (Not in the original this was a class, I invented using interfaces - yay me):
public interface MyTable {
public static String TABLE_NAME = "MyTable";
public static String COL_1 = "Col1";
public static String COL_2 = "Col2";
}
Okay, we're nearly ready to write some code:
public class MyTableDao {
public MyObject selectObject(String selector) {
String[] columns = {MyTable.COL_1, MyTable.COL_2);
Object[] types = {DbTypes.DATE, DbTypes.INTEGER};
String[] whereClause = {MyTable.COL_1};
Object[] whereValues = {selector};
MySqlFactory sqlFactory = new MySqlFactory();
String sql = sqlFactory.createSql(columns, whereClause);
PreparedStatement ps = connect.prepareStatement(sql)
MySqlInjector.injectValues(ps, new Object[] {selector});
ResultSet rs = stmt.executeQuery(query);
MyObject[] objs = MyObjectFactory.getObjects(rs, columns, types);
}
Yeah, I think that was it. I'm writing from memory from 5 years ago so forgive if errors.
Key standards:
* List the columns to retrieve
sb.append(DbConsts.SELECT).append(" ").append(COL).append(,) ...
* List the types of the columns so an object factory can retrieve the right type
if(type[0]==DbTypes.INTEGER) rs.getInt();
* List the WHERE clauses
sb.append(DbConsts.WHERE).append(" ").append(clauses[0]...
I forgot about the static object factories until now. I suspect the SQL factories were static rather than implementations of an interface. It was more by program by convention.
The rest of the implementation is left as an exercise for the reader.
Can we stop all this nonsense with Hibernate now please?
|
|
|
|
|
|
Absolutely not. No joke. Can't remember/was never sure whether people accepted it because of the designer's standing in the company or because they didn't realise.
|
|
|
|
|
I had to do stuff like that in the 90s when my company was playing musical chairs with SQL vendors.
Our server side apps ended up being able to run seamlessly on SQL Server, Sybase and Oracle. That probably doesn't sound like a big deal now, but driver standardization wasn't so far along when NT 3.51 was king.
Under the hood, the Microsoft code does similar tasks to what is suggested here...
|
|
|
|
|
Seems legit...
It's an OO world.
public class Naerling : Lazy<Person>{
public void DoWork(){ throw new NotImplementedException(); }
}
|
|
|
|
|
Very good idea in 2005, but we can now forget all this old rubbish, as we have LINQ.
Hallelujah !!
Yes I did have to look up the spelling..
|
|
|
|
|
I'm not sure it ever was a good idea to be honest. This was in 2005 (+- 2 years) and I joined this company after two years of using Hibernate. I was using it for batch analysis and complex relationships without too much issue (caching being an exception)
I still think that writing out the sql by hand would have made better reading/maintenance and in fact used to convert these DAOs to manual ones when I could.
This is an example of "future-proofing" that definitely made things worse.
Next example would have to be 9-10 layers (DAO, BO, Service, Process, Application, Remoting, Command, Action, User Validation) when 3 or 4 would have done. Or maybe passing a user id in every method signature in case we needed to apply security. Or perhaps a utility class with 100+ unrelated methods. Or was it the 3000 line classes. No, I remember - creating copy books for the mainframe.
|
|
|
|
|
I agree sometimes less is more.
I am currently writing a DB connected WPF app using Entity Framework and Linq for DA. I started out using Stored Procedures with EF, and then abondoned the idea due to currupted Entity Data Objects when re-adding edited SPs. I now just use LINQ for all DA, and find it much less stressful than dealing directly with the database myself.
Saying that creating dynmanic entity connections was a bit problematic.
As for security, I am using singleton patterns to store commonly used global properties. This cuts down on DB access.
I'm finding the whole dev process is much faster this way, and easier to structure.
|
|
|
|
|
Yes, so now we have people making some new abstraction to what doesn't need abstracted. The urge of in house programmers to design a framework is strong. Why use a single class to wrap something if one can use four or five? And while we are at it, we can make it easy to do things we never do anyway. If we get on a roll, perhaps the architect who clearly doesn't get writing code will adopt a new standard of crap.
|
|
|
|
|
The DAO that can be implemented, is not the true DAO.
|
|
|
|
|
Am I even partially responsible for this?
Well, I've seen this type of "DAOs" around in many legacy ASP.NET projects I've worked with. It's strange how the newest one had been written during the last months of 2010.
modified 6-Apr-21 21:01pm.
|
|
|
|
|
This only appears to be a good idea when you are sleep deprived and working on your project at 4am. That's why I drink more coffee. I had to support code like this a few times. Good luck if you are trying to debug the query, especially if the person who wrote it used column indexes instead of column names when referencing the result set.
|
|
|
|
|
Turf Monster wrote: Good luck if you are trying to debug the query
I was reading through the comments and thinking exactly this before encountering your post. (Shudders at the memory).
|
|
|
|
|
At first I was prepared for a flame against the ancient-of-days DAO from Microsoft, which was the data access interface for Access. At least that interface (and still is) useful in a wide variety of applications where the coder is also the database interface guy.
The only time I've ever developed such an abstract interface was for a code transition from the use of Raima's dbVista network-model database to relational DB2 for OS/2. But then, most of the code I write these days is either for SQL Server or for Access but never for both at the same time. I know there are many valid reasons for using LINQ and Entity Framework, particularly if you have a large project where the coders have little or no ability to add or modify the functionality of an existing database, and also for data storage mechanisms that you want to access as if they were relational when in fact they are anything but - and boy am I glad not to be working in projects like that at the moment
|
|
|
|
|
Works for me.
I write mostly desktop applications using C# (VS2008). Over the last several years I've built a utility library that abstracts SQL access into a very simple set of routines that I use in all my applications. The work is all done. I can very simply connect and interact with SQL databases without having to forever call up a help reference or reconstruct a connection string. The library was fun to develop and debug and now sits there waiting for me to use it when developing another application.
>>It's surprising how many people try to create their own solutions to problems which already have established and well-behaving solution<<
Yeah ... I developed it myself because I wanted a SIMPLE way to do all that. The .Net Framework has tons of features built-in but nothing approaching the simplicity of what I built myself. This is surprising? Not to me. Mature developers have always abstracted "library" code for their own use so they could avoid re-inventing the wheel in SOMEONE ELSE's image.
-Max
|
|
|
|
|
I agree... I have also written myself plenty of utility libraries over the years writing desktop applications, server software and web applications.
The simple reason why... I want a way that works similar over all my enviroments, and one that *I* can debug if something seems to be going wrong... Or the newest release of PostgreSQL rolls around and they decide implicit casting is evil again
So yes, I have written my own abstractions because they work the way I think, not the other way around
|
|
|
|
|
Idunno. My experience is that you mostly have to write only a little glue code which you reuse across LOB apps. The libraries which do real work are in most cases already available. Re-inventing your own custom-built wheel is the wrong kind of laziness - your wheel will need more maintenance than a thin adapter to something built and maintained by others.
|
|
|
|
|
The problem with other people's solutions always is that they are other people's solutions. They usually solve their problems and never quite yours and they do it in a way that suited them best. That's why I usually don't like 'Design by Frankenstein' where your project ends up only being the glue code between every framework and library that could be found.
An example? In a project we needed access to an already existing database. Its design was certainly not the best, but we had no other choice than to use it as it was. Trying to use Hibernate for this was one big mistake. The mappings were correct, but Hibernate constantly was nagging us to improve the database's design and that we should do this and not do that. It took a few creative workarounds to find mappings that Hibernate could live with. I could have found better uses for the time that took than getting a 'smart' framework to do its job.
And let's not forget that (understandably) that each library or framework was designed to solve a very central and important task, so the designers thought a certain use of resources (like memory and the CPU) to be justified. The problem is that all the other frameworks were seen to be just as important by their designers and you can quickly end up with a slow memory hog.
Its hunger for memory was the reason why Hibernate was thrown out of the project. It quickly went through the roof (i.e the server process' memory limit) before even a medium size job was successfully done. Totally unacceptable in productive use when several such jobs could be running at the same time. The problem was the persistance feature of Hibernate, which hogged the memory and was not really needed there. Using a stateless session was impossible because it did not have some of the features we needed.
In the end we threw out the complete resource access layer and Hibernate with it and built a new one with simple ADO.Net queries. We slammed that thing together in two days and it's working without any issues since then and the memory consumption while processing the jobs now is a constant level line.
So, if you ask me, simply go for the most direct way you can and use shiny frameworks sparingly and only after thinking through all possible aspects.
I'm invincible, I can't be vinced
|
|
|
|
|
I agree with you particularly in the case of entity modelling/persistence frameworks. The way someone else thought you should do something is almost never exactly how you have actually done it, particularly if you come in at the point where there already is a database (possibly many years old).
I've had fun fighting NHibernate on a previous project where the database predated the mappings, and in a couple of cases I gave up and just used a direct SQL query because it was much quicker and easier to do so.
In another recent project, which was very short, I wrote a simple entity persistence framework because it was much easier to do that than to work out how to make Entity Framework actually do what we wanted.
Frameworks work really well if you agree at the start that you're going to do things their way. Often that's not possible and, particularly with database mapping but to a lesser extent with every situation, the framework is too hard to adapt to a different situation.
There are a few hard problems out there for which a framework is a timesaver. UI is the obvious big one, and serving content over HTTP is another one. Web services would come in this category too. It's not worth rewriting these, because to do so, even for the limited scope of a single project, would take weeks. But in the case of entity mapping, implementing what you need for your project will probably take a few days, and that's likely to be less time than you'd waste trying to get an existing framework to play nicely with the rest of your code and with your database.
|
|
|
|
|
BobJanova wrote: There are a few hard problems out there for which a framework is a timesaver. UI
is the obvious big one, and serving content over HTTP is another one. Web
services would come in this category too. It's not worth rewriting these,
because to do so, even for the limited scope of a single project, would take
weeks
You are joking, right? Wait a second, I just need to make some new screenshots of the program I'm just working on...
Edit: You can see a bit more when you click on the images and enlarge them to full size
Screenshot 1[^]
Screenshot 2[^]
The project has four webservices, but they are of the normal .Net flavor. There also is a graphics engine for XNA. And the UI I have been working on for a while. At the moment there is no way to control what the 3D engine is doing, so the background is still a little boring. At the moment it just shows two of my first own 3D models to see that the rendering thread is still with us. The two screenshots show the first fully functional views for registering a new user and logging in.
Anyway, I must disagree with you when it comes to writing your own UI
I'm invincible, I can't be vinced
|
|
|
|
|
I should have specified, UI for normal desktop applications. UI within a graphics engine context like this is different, I just wasn't thinking along those lines when I posted before. The difference is that you want your UI to look, well, yours, whereas with a normal application you want it to look like a standard (insert OS here) application so using the standard framework gives you what you want.
I've written personal UI frameworks for games before as well, most recently in Flash (so I don't have to stick megabytes of Flex on my project just to click some buttons).
|
|
|
|
|
Well, it's not just a collection of classes that are hard coded just for this project and act like controls. It implements the interfaces of our older MVP framework, so that we can port the logic from the earlier WPF version. The views are declared with XAML and you have control over every design aspect of the controls. You can completely change the appearance and behavior of all controls from your code, in XAML or in styles and themes. By now the work on the UI has slowly shifted away from the fundamental things to adding more controls or adding more design options to existing controls.
You are right, under normal circumstances I would not have done this myself. But it was interesting to do, especially because I would never get to do it under normal circumstances. Just to think of the heart attack my boss would get if I ever proposed such a thing at work
What did it cost? Lot's of time. What did I gain? I learned a lot and I have a little UI that is mature enough to slowly be of use. And most of all, it's mine. I can give it away or charge insulting prices for it. And nobody in Redmond or elsewhere gets to decide when it's time to stop supporting it
I'm invincible, I can't be vinced
|
|
|
|
|